Re: [SQL] installing pgaccess

2000-09-17 Thread Peter Eisentraut
Keith Wong writes: > Anybody know how to compile pgaccess from postgres source files? > It must be a configure option, but I can't find it. --with-tcl --with-x -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] transactions surrounding extension functions

2000-09-28 Thread Peter Eisentraut
action, thereby including all the queries composing my > function in that same transaction? Yes -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] invoke an external shell script from a function

2000-09-29 Thread Peter Eisentraut
Chau, Artemis writes: > Does anyone know how to invoke an external shell script from a function when > it is called from a sql statement? If it's a C function, use system("file"). -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Transaction isolation level for plpgsql

2000-09-30 Thread Peter Eisentraut
Keith Wong writes: > Does anybody know the transaction isolation level default when a plpgsql > stored procedure is called? > Is it possible to set it? Or is this controlled by SPI? It's whatever the transaction that called the function was set to. -- Peter Eisentraut [E

[SQL] Re: [GENERAL] All function parameters become NULL if one is?

2000-09-17 Thread Peter Eisentraut
value is also null. > Is this intentional? Is there a way around this? Wait for 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] createuser

2000-10-04 Thread Peter Eisentraut
Craig May writes: > createuser -adq username > > returns > > invalid option adq createuser -a -d -q -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Granting of permissions on tables

2000-10-05 Thread Peter Eisentraut
Saltsgaver, Scott writes: > GRANT ALL on to ; > > after running the command I lost permissions to the tables once I exited > psql. I had to run psql as the postgres user to correct the situation. > > Is this a bug or desired behavior? It's a bug. Fixed for 7.1

Re: [SQL] Standard syntax?

2000-10-12 Thread Peter Eisentraut
Franz J Fortuny writes: > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where etc. > is this a "standard" or ANSI 9X compatible syntax Yes. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] problem with select where like ']'

2000-10-18 Thread Peter Eisentraut
Joseph Shraibman writes: > The only way I can find is to do a search on something, and select to > search in mailing lists. Then after the search returns click on a link, > and trucate the url to http://www.postgresql.org/mhonarc/ When in doubt, use geocrawler.com. -- Peter E

Re: [SQL] Alternate Database Locations

2000-10-26 Thread Peter Eisentraut
; Can't create pid file: /usr/local/pgsql/data/postmaster.pid > Is another postmaster (pid: 10686) running? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Synonyms

2000-10-30 Thread Peter Eisentraut
a query from mulitple databases. ( the usual > DB.Table or DB:table or DB@table dont seem to work) Nope. It's not really planned either in case you wanted to ask. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Postgres 7.0.X and arrow keys

2000-11-21 Thread Peter Eisentraut
Antti Linno writes: > I installed new Mandrake 7.2 and was eager to try new postgres. So I > installed it and used pgsql. What surprised me was that the arrow keys > wouldn't work anymore as history, instead I get those ascii codes. You need to install the readline-devel R

Re: [SQL] plperl

2000-10-26 Thread Peter Eisentraut
Jie Liang writes: > "../../../src/Makefile.global", line 304: Need an operator > make: fatal errors encountered -- cannot continue > su-2.04# > > what I need to do? Use GNU make. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Decimal vs.Numeric vs. Int & type for OID

2000-12-13 Thread Peter Eisentraut
ent to numeric. The oid type should only be used as a foreign key to an oid column. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Documentation for CASE

2000-12-16 Thread Peter Eisentraut
Albert REINER writes: > I think that the documentation for CASE is not very clear: Maybe you will like this better (from upcoming 7.1): http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.htm -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] trying to pattern match to a value contained in a column

2000-12-07 Thread Peter Eisentraut
ERE a.long_name like (b.abbr || '%'); -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] MD5 use in PL/Perl

2000-12-28 Thread Peter Eisentraut
, RFC) and make a C function wrapper around it. Incidentally, someone has already done this for the upcoming 7.1 release, but since the function call interface has changed the back port won't be trivial. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Rules

2000-12-31 Thread Peter Eisentraut
Peeter Smitt writes: > CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD > SELECT fun1(new); > > Thing is that backend gives this error. > ERROR: parser: parse error at or near ")" Try double quoting "new", since it's a reserved word. --

Re: [SQL] Looking for comments

2001-01-02 Thread Peter Eisentraut
ribution there's a directory contrib/isbn_issn that defines a couple of data types that might help you. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] psql -f option

2001-01-04 Thread Peter Eisentraut
: $ psql -f /etc/sysctl.conf psql:/etc/sysctl.conf:8: ERROR: parser: parse error at or near "#" It looks to me like you are using an older psql, perhaps from a previous installation. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Casting

2001-01-05 Thread Peter Eisentraut
;s before I can do the calulations. Depends on the calculation. I'd just try to see if it works. When in doubt add casts. > Also I looked in the User manual but could not find the modulo function > where is it ? 5 % 4 mod(5, 4) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] how to select a time frame on timestamp rows.

2001-01-13 Thread Peter Eisentraut
ke this: > select User_Name from tbacct where acct_timestamp like '2000-11%' group > by User_Name; select user_name from tbacct where extract(month from acct_timestamp) = 11 ... (SQL compliant) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] single byte unsigned integer datatype

2001-01-14 Thread Peter Eisentraut
byte unsigned integers, only single byte signed characters. > Is there a better datatype to use? smallint with a check constraint -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

[SQL] Re: [INTERFACES] outer join in PostgreSql

2001-01-15 Thread Peter Eisentraut
Mauricio Hipp Werner writes: > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? No symbol, just words. http://www.postgresql.org/devel-corner/docs/postgres/sql-sel

Re: [SQL] select returns no line

2001-01-23 Thread Peter Eisentraut
ESjZo| > (1 row) Yep, the update will fix the corrupted index (at least to the extent that this particular case now works). -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [HACKERS] wrong query plan in 7.1beta3

2001-01-27 Thread Peter Eisentraut
ow estimate is way off (1000 vs 6) and thus a sequential scan is (correctly) thought to be faster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] binary operators

2001-02-02 Thread Peter Eisentraut
Frederic Metoz writes: > I am looking for the binary AND and OR ... SHIFT as well. > Do they exist for postgresql ? in 7.1 -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Suggestion for psql: --file -

2001-02-02 Thread Peter Eisentraut
ot very clear: it took me > some experimentation to find out that you have to do "\set VARIABLE" > interactively or give "--set VARIABLE=" to set a variable that does > not take a value. Suggested new wording? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] 7.0.2-docs: textpos -> strpos

2001-02-02 Thread Peter Eisentraut
Albert REINER writes: > in the 7.0.2-docs I find the function textpos: > However, in psql it seems one has to use strpos: textpos() was removed from the 7.1 documentation. position() is the SQL function, I think strpos() is from Oracle. -- Peter Eisentraut [EMAIL PROTECTED]

Re: [SQL] Suggestion for psql: --file -

2001-02-03 Thread Peter Eisentraut
Albert REINER writes: > > Suggested new wording? > > What about: Works for me. Thanks. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] SQL Join - MySQL/PostgreSQL difference?

2001-02-05 Thread Peter Eisentraut
showing the tables and the data in it, it's fairly hard to tell. I think MySQL does case insensitive string comparisons; check that. > I read a post about PostgreSQL not supporting outer joins, but I don't > have enough experience with SQL to determine if this is such a query &g

Re: [HACKERS] PL/pgsql EXECUTE 'SELECT INTO ...'

2001-02-08 Thread Peter Eisentraut
regret it later. You can always use CREATE TABLE AS. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Wierd postgres Problem

2001-02-12 Thread Peter Eisentraut
> cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "not" -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] combining

2001-02-12 Thread Peter Eisentraut
hese into one query? select table1.id from table1, table2 where table1.id = table2.id and lastname = 'morton' and type='pie' and content = 'apple'; Or in 7.1, optionally: select id from table1 natural join table2 where lastname = 'morton' and type ='pie&

Re: [SQL] PL/PGSQL Cook Book

2001-02-12 Thread Peter Eisentraut
to the command line for debugging purposes? >How to debug PL/PGSQL? >Various examples for each of the statements All valid questions. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] COPY isn't working right for me

2001-02-13 Thread Peter Eisentraut
e" > > I'm assuming this has to do with the member_id with > type serial. How do I import into this without having > to add the OID's to each of the rows in the text file? You can't, using COPY. You'll have to preprocess your file, either into INSERT statements, are prepend your own id's. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] cannot create sequence

2001-02-14 Thread Peter Eisentraut
Olaf Marc Zanger writes: > as it seems postgresql 7.0 has trouble to create > > ver_id_seq Define "trouble". -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

[SQL] Re: [ADMIN] TPCH questions

2001-02-15 Thread Peter Eisentraut
Victor Muntes Mutero writes: > select * from (select * from supplier); (for example) No subselects in FROM before 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Contributing Documentation to PG

2001-02-19 Thread Peter Eisentraut
y. > I know I can just use the Makefiles with a little tweaking, but I am > wondering if I am missing something here. No tweaking should be required, if you have your SGML catalogs set up correctly. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] sum(bool)?

2001-02-23 Thread Peter Eisentraut
Olaf Zanger writes: > i'd like to add up the "true" values of a comparison like > > sum(a>b) sum(case when a>b then 1 else 0 end) of maybe even just select count(*) from table where a>b; -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] Weird NOT IN effect with NULL values

2001-03-01 Thread Peter Eisentraut
LSE in a WHERE condition, so no rows are returned. Note that 'xxx = NULL' is different from 'xxx IS NULL'. Also note that NULL is not the same as FALSE in general. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] SQL copy from csv with explicit field ordering

2001-03-02 Thread Peter Eisentraut
this will probably do: #! /bin/sh IFS=, cat "inputfile" | \ while read COL1 COL2 COL3; do echo "$COL3,$COL2,$COL1" >> "outputfile" done -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)---

[SQL] Re: [GENERAL] Date question

2001-03-06 Thread Peter Eisentraut
t; In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. How about CURRENT_DATE + INTERVAL '12 months'? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP

Re: [SQL] cannot get CREATE TABLE AS to work

2001-03-09 Thread Peter Eisentraut
uestion, should this really be taking 3 hours to insert 315446 > records? I noticed the disk is basically idle during the few times when I > watched. Would this be because of the index created on obs_id? Quite likely. Also make sure you wrap the inserts into a BEGIN/COMMIT block. --

Re: [SQL] recompiling to use gnu readline?

2001-03-10 Thread Peter Eisentraut
ecompile. > If I need to recompile, how can I do so without wiping out > existing data? Just don't wipe it out. You can use the new executable with existing data. In fact you only need to recompile psql (src/bin/psql) after re-configuring, not the postmaster. -- Peter Eisentraut

Re: [SQL] PL/PgSQL and NULL

2001-03-11 Thread Peter Eisentraut
lving NULLs don't work well before version 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregist

Re: [SQL] Fwd: Silly question about numbering of rows?

2001-03-13 Thread Peter Eisentraut
terface has a "fetch"-like approach, it seems trivial to keep a counter. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] PL/pgSQL "compilation error"

2001-03-14 Thread Peter Eisentraut
thor expressed interest in more work on the PL/pgSQL documentation; we'll see what comes of it. http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)-

Re: [SQL] Postgres & XML

2001-03-19 Thread Peter Eisentraut
seful. See <http://xml.apache.org/cocoon/index.html>. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister Your

Re: [SQL] Serials.

2001-03-24 Thread Peter Eisentraut
you reset the sequence in the first place? You should probably set it back to where it was (using setval()). Sequences simply return incrementing numbers, they don't fill holes or have any constraint-avoiding logic. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [HACKERS] triggered data change violation

2001-03-20 Thread Peter Eisentraut
en, but I recall there were some implementation and definition problems with deferred constraints. ...FAQ alert... -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through

Re: [SQL] Help

2001-03-25 Thread Peter Eisentraut
Mohamed ebrahim writes: > I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. Use a cron job. -- Peter Eisentraut [EMAIL PROTECTED

Re: [SQL] paging

2001-03-26 Thread Peter Eisentraut
it did , I would have to disable it in order to > redirect the output to a file or a pipe. Try psql -P pager to turn the pager off. Also you can probably use export PGPASSWORD=xyz psql ... to skip the password probe. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: Calling Java from psql (was Re: [SQL] requesting help)

2001-03-26 Thread Peter Eisentraut
Mathijs Brands writes: > Has anybody ever tried calling Java code from a pgsql trigger written > in C? Shouldn't this be possible using JNI? I have, and given the current Java implementations it's a desaster. -- Peter Eisentraut [EMAIL PROTECTED] http:

Re: [SQL] Replace into...?

2001-03-27 Thread Peter Eisentraut
rmutation thereof. If you need to do a lot of this, writing a PL/pgSQL function might be worthwhile. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once wit

Re: Calling Java from psql (was Re: [SQL] requesting help)

2001-03-29 Thread Peter Eisentraut
n also take a look at my little toy project PL/sh: http://www.postgresql.org/~petere/plsh.html -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please s

Re: Calling Java from psql (was Re: [SQL] requesting help)

2001-03-29 Thread Peter Eisentraut
non-database world with the database is definitely real. But usually a regular function call near the end of the transaction block is much more appropriate than a trigger function. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/

Re: [SQL] 'Include' function in SQL scripts

2001-03-30 Thread Peter Eisentraut
Alessio Bragadini writes: > I was wondering if PostgreSQL supports some kind of #include between SQL > script files, to split a long script in different files. If you're using the psql client then \i filename can be used. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.

Re: [SQL] Max Size of a text field

2001-03-30 Thread Peter Eisentraut
1: increase BLCKSZ in src/include/config.h Option 1.5: use lztext (7.0* only) Option 2: upgrade to 7.1 -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives

Re: [SQL] primary key scans in sequence

2001-03-30 Thread Peter Eisentraut
ger comparison with an index scan on a bigint column. Quoting the number, '833228', should fool it sufficiently to make this work. Incidentally, it seems unlikely that you need to use bigint for membership ids, unless you plan on more than 2*10^9 members. -- Peter Eisentraut [EMAIL

Re: [SQL] please help

2001-04-05 Thread Peter Eisentraut
Loïc Bourgeois writes: > What is the equivalent of the oracle request: SELECT ... FOR UPDATE > NOWAIT, under PostGreSQL I don't know Oracle, but there doesn't seem to be such a command in PostgreSQL. If the table is already locked, the SELECT FOR UPDATE has to wait. --

Re: [SQL] how to do this join ?

2001-04-06 Thread Peter Eisentraut
get the > definition (clear text) from another table. > > The query returns that typ is not known . -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Casting numeric to text

2001-04-08 Thread Peter Eisentraut
Hans-Jürgen Schönig writes: > Is there any possibility to cast numeric to text in Postgres 7.0.3? > > shop=# select cast(price as text) from products; > ERROR: Cannot cast type 'numeric' to 'text' Use the to_char() function. -- Peter Eisentraut [EMAI

Re: [SQL] open and closed paths ...

2001-04-08 Thread Peter Eisentraut
part from ancient tradition, paths enclosed in parentheses, like ((3,1),(2,8),(10,4)), are implicitly closed. To make an open path brackets should be used, like [(3,1),(2,8),(10,4)]. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---

Re: [SQL] Casting numeric to text

2001-04-09 Thread Peter Eisentraut
eature that is supposed to use ::. (It escapes me at the moment what that was.) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] please help

2001-04-09 Thread Peter Eisentraut
a second level of locks to protect the information you obtained regarding the "real" locks. I'm not saying it's impossible, but 20 years ago people were writing Ph.D. theses about these sort of things. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ --

Re: [SQL] Re: open and closed paths ...

2001-04-09 Thread Peter Eisentraut
)']; > ERROR: parser: parse error at or near "[" > shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]'; > ERROR: parser: parse error at or near "'" > shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]'::path; > ERROR

Re: [SQL] Casting numeric to text

2001-04-09 Thread Peter Eisentraut
Ross J. Reedstrom writes: > FYI, I can't find an occurance of '::' that's not part of '::=' in either > SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got. SQL 1999 6.12 ::= [ ] That syntax even makes sense... -- Peter Eisentraut

Re: [SQL] Re: select substr???

2001-04-09 Thread Peter Eisentraut
; > > what I want to do is 'select distinct(data) [ignoring non alphanumeric > characters] order by data' Write a function that strips off the suffixes and do 'select distinct voodoo(data) ...'. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e

Re: [SQL] Making SELECT COUNT(seed) FROM fast

2001-04-11 Thread Peter Eisentraut
ware speed. You might be able to speed it up a little by using count(*) instead. Note that there's a semantic difference, because count(seed) doesn't count the rows where seed is null, which is probably not what you intended anyway. -- Peter Eisentraut [EMAIL PROTECTED] http

Re: [SQL] DROP TABLE in transaction

2001-04-12 Thread Peter Eisentraut
ld be rollback-able, > dropping a table would be the first on the list. Naah. Insert and update are first. ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked ou

Re: [SQL] enumerating rows

2001-04-12 Thread Peter Eisentraut
27;s no use of the server generating this information, because the numbering is implied by the order in which the rows are sent. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] BOOLEAN data type?

2001-04-13 Thread Peter Eisentraut
> However, in testing (7.1 RC2), the query turned out to mean this: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required IS NOT NULL; No way. You're doing something wrong. How about showing the data that makes you believe this? -- Peter Eisentraut

Re: [SQL] Using Random Sequence as Key

2001-04-16 Thread Peter Eisentraut
i 72605 | there 83469 | blah (3 rows) Verifying the default expression for correct boundaries is left as an exercise. ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you check

Re: [SQL] Using Random Sequence as Key

2001-04-16 Thread Peter Eisentraut
; > >INSERT 36164 1 > >=> insert into test (content) values ('blah'); > >INSERT 36165 1 > >=> select * from test; > > id | content > >---+- > > 61616 | hi > > 72605 | there > > 83469 | blah > >(3 rows) > __

Re: [SQL] function to format floats as money?

2001-04-17 Thread Peter Eisentraut
actness of storage and arithmetic, it might even be illegal if you're using it for official purposes. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] is this proper sql?

2001-04-17 Thread Peter Eisentraut
clayton cottingham writes: > hi one of the developers here at work say this should work > insert into detail (det_id,det_mas_id,det_date,det_amt) values > (0,0,now(),'0'), (1,1,now(),'1'); This is real SQL (except for the now() function) but it isn't suppo

Re: [SQL] RI permission problem

2001-04-25 Thread Peter Eisentraut
them rather than the user who is > executing them. This would help greatly in closing some security holes > like this we are dealing with. It wouldn't be hard to implement, but there were some disputes about the particular method of how to do it. Maybe 7.2. -- Peter Eisentraut [

Re: [SQL] RI permission problem

2001-04-25 Thread Peter Eisentraut
Kyle writes: > Peter Eisentraut wrote: > > > Kyle writes: > > > > > Is there a way to get this to work without granting update to table b? > > > > Update to 7.1.] > > I'm on 7.1. Should an RI trigger under 7.1 run as the DBA or as the curr

Re: [SQL] SET SEQSCAN TO OFF - error

2001-04-26 Thread Peter Eisentraut
now why? Because 'seqscan' is not a valid option name. Maybe you mean enable_seqscan. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] simulate union in subselect

2001-04-26 Thread Peter Eisentraut
#x27;t actually tried whether this does not work, but ISTM that you could simply do select id, recv_date as date from table1 union select id, send_date as date from table2 order by 2; and ignore the second column when processing the result. -- Peter Eisentraut [EMAIL PROTECTED] http

Re: [SQL] Using Transaction Blocks w/ SELECT

2001-04-27 Thread Peter Eisentraut
it. Am > I missing something? Any help appreciated. It's done to get consistent data snapshots across several commands. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you check

Re: [SQL] '13 months ago'::reltime

2001-05-05 Thread Peter Eisentraut
Christopher Sawtell writes: > On Friday 04 May 2001 06:32, you wrote: > > Type reltime is old and deprecated. Don't use it. > > What should we be using instead? interval -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter

Re: [SQL] RI permission problem

2001-05-07 Thread Peter Eisentraut
Kyle writes: > Shouldn't the select access to the view trickle down to subordinate select functions? I would think not. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don&#x

Re: [SQL] Is function atomic?

2001-07-07 Thread Peter Eisentraut
isted there: that's why I asked. Is everything on that list planned > for 7.2? No. The TODO list is just a list of random ideas, some better than others. Most seasoned developers have their own private lists of things that they would like to get done. -- Pete

Re: [SQL] PL/TclU

2001-07-06 Thread Peter Eisentraut
afe to offer it to normal users. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Is function atomic?

2001-07-06 Thread Peter Eisentraut
ide the database may obviously have different semantics. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail c

Re: [SQL] Is function atomic?

2001-07-06 Thread Peter Eisentraut
, but there may be issues if you modify global state or there is a serialization failure. These are the same issues that you have to deal with in any programming environment. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)-

Re: [SQL] Is function atomic?

2001-07-06 Thread Peter Eisentraut
n? See LOCK command. But note that this would lock out process in this function or any other access to these tables. Maybe you should take a look at the userlock module in contrib. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter

Re: [SQL] While Using COPY COMMAND ...

2001-07-06 Thread Peter Eisentraut
server is probably running under a different user id (postgres?). -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

Re: [SQL] finding current oid

2001-07-06 Thread Peter Eisentraut
postgresql writes: > Is there a way to return the current oid of a transaction? Transactions don't have oids, only table rows do. The libpq function PQoidValue() will possibly give you the last affected oid. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.ne

Re: [SQL] CREATE TYPE function examples

2001-07-10 Thread Peter Eisentraut
is seems to be outdated regarding the fmgr update, though. (Hint, hint...) -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister com

Re: [SQL] ERROR: Procedures cannot take more than 16 arguments

2001-07-10 Thread Peter Eisentraut
the comments there. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

Re: [SQL] can we write to a flat file from Postgresql procedure

2001-07-11 Thread Peter Eisentraut
R Vijayanath writes: > It would be great if you can tell me if I can write a > procedure that can write the output to the OS(Linux > OS) file. You could try out PL/sh for that. http://www.postgresql.org/~petere/plsh.html YMMV -- Peter Eisentraut [EMAIL PROTECTE

Re: [SQL] How can we match a condition among 2 diff. tables?

2001-07-13 Thread Peter Eisentraut
ee WHERE emp_id NOT IN (SELECT emp_id FROM salesorder); or, slightly uglier but possibly faster SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM salesorder WHERE salesorder.emp_id = employee.emp_id); -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter --

Re: [SQL] Hey! ORDER BY in VIEWS?

2001-07-15 Thread Peter Eisentraut
Josh Berkus writes: > Hey! I thought you couldn't do ORDER BY in views ... yet I just did. > Is this a new thing, or am I just getting my Trasact-SQL and my > PostgreSQL mixed up again? I think it was allowed from 7.1 on to enable LIMIT in views to work sensibly. -- Peter Eisen

Re: [SQL] Cross database foreign keys

2001-07-06 Thread Peter Eisentraut
Morgan Curley writes: > Does anyone know if it is possible to connect to a differernt db from > within a plsql function. > I have multilple inter-related schemas and want to enforce some fk > relationships. Not possible -- Peter Eisentraut [EMAIL PROTECTED] http://funktur

Re: [SQL] interpreting attributes in pg_class

2001-07-16 Thread Peter Eisentraut
ge/docs/7.1/postgres/catalog-pg-class.html -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] First steps in plpgsql - language not recognized?

2001-07-17 Thread Peter Eisentraut
man createlang -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)

2001-07-17 Thread Peter Eisentraut
Chris Ruprecht writes: > I need to know how I can access a flat file from within a PL/PGSQL script. You can't. PL/TclU could help you there. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of b

  1   2   3   >