[SQL] Having elements of an int[]-array reference other tables

2008-01-21 Thread Andreas Joseph Krogh
Hi. Is it possible in PG to have elements in an int[]-array reference another int-column in another table? What I want is it to behave as a foreign key. create table master( my_id integer primary key ); create table test( id serial primary key, id_array integer[] references master(my_id) ); It

Re: [SQL] Having elements of an int[]-array reference other tables

2008-01-21 Thread Pavel Stehule
Hello, it isn't possible without custom triggers. regards Pavel Stehule On 21/01/2008, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > Hi. > Is it possible in PG to have elements in an int[]-array reference another > int-column in another table? What I want is it to behave as a foreign key.

Re: [SQL] transaction and triggers

2008-01-21 Thread Gerardo Herzig
D'Arcy J.M. Cain wrote: On Fri, 18 Jan 2008 12:16:04 -0300 Gerardo Herzig <[EMAIL PROTECTED]> wrote: Right. But today, that trigger do some other work, wich includes writing some files to disk, so there is my problem. Crap, i guess i will have to review the main logic. I built a re

Re: [SQL] (possible) bug with constraint exclusion

2008-01-21 Thread Christian Schröder
Hi list, It's a bug, it's patched: http://archives.postgresql.org/pgsql-committers/2008-01/msg00184.php I have just stumbled on the same bug today and was very happy to find a patch; however, I have a 8.2.6 server running which of course cannot be patched. (According to the CVS tags the revi

Re: [SQL] (possible) bug with constraint exclusion

2008-01-21 Thread Tom Lane
=?ISO-8859-1?Q?Christian_Schr=F6der?= <[EMAIL PROTECTED]> writes: >> It's a bug, it's patched: >> http://archives.postgresql.org/pgsql-committers/2008-01/msg00184.php >> > I have just stumbled on the same bug today and was very happy to find a > patch; however, I have a 8.2.6 server running which

Re: [SQL] (possible) bug with constraint exclusion

2008-01-21 Thread Christian Schröder
Tom Lane wrote: Better look again. Sounds like a sensible advice ... I somehow managed to find http://archives.postgresql.org/pgsql-committers/2008-01/msg00183.php instead of http://archives.postgresql.org/pgsql-committers/2008-01/msg00184.php ... Sorry for that! Regards, Christian -

Re: [SQL] improvements to query with hierarchical elements

2008-01-21 Thread Steve Midgley
Date: Sun, 20 Jan 2008 20:01:08 -0800 From: Ryan Wallace <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Subject: improvements to query with hierarchical elements Message-ID: <[EMAIL PROTECTED]> Greetings, I have a complex query which I am trying to figure out the most efficient way of perfo

[SQL] currval() within one statement

2008-01-21 Thread silly_sad
Helo is it expected that the currval() changes its value between calls within one statement ? Look the following call: INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2; Where the trigger before insert on ttt is defined and this trigger calls nextval('ttt_id_seq'). I

Re: [SQL] currval() within one statement

2008-01-21 Thread sad
A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want to discuss the titled problem or to

Re: [SQL] currval() within one statement

2008-01-21 Thread A. Kretschmer
am Tue, dem 22.01.2008, um 10:16:30 +0300 mailte silly_sad folgendes: > Helo > > is it expected that the currval() changes its value between calls within > one statement ? > > Look the following call: > > INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2; This fails if you