From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexander Stanier <alexander.stanier@egsgroup.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Referential Integrity
Date: Mon, 10 Feb 2003 10:09:52 -0500
Message-ID: <5191.1044889792@sss.pgh.pa.us> (raw)
In-Reply-To: <D94218452A34444B8C61D34462655B0A7E1E34@egssrv01.egsgroup.com>
References: <D94218452A34444B8C61D34462655B0A7E1E34@egssrv01.egsgroup.com>
"Alexander Stanier" <alexander.stanier@egsgroup.com> writes:
> [ expected ON DELETE CASCADE doesn't seem to happen in this context: ]
> CREATE FUNCTION test() RETURNS INT4 AS '
> DECLARE
> v_return INTEGER;
> BEGIN
> DELETE FROM alex
> WHERE aid =3D ''1''
> AND bid =3D ''1'';
> INSERT INTO alex (aid,bid,itemdesc)
> VALUES (''1'',''1'',''OneOne'');
> INSERT INTO alexette (aid,bid,vcode)
> VALUES (''1'',''1'',''V'');
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
The cascaded deletes will be implemented at end of statement --- which
I believe is always taken to be the end of the current interactive
statement, ie, the "SELECT test()" you typed. So they haven't happened
yet at the time the function tries to do the inserts.
This is probably a bug, but IIRC, it's not entirely obvious what to do
instead. I seem to recall some inconclusive discussions in pgsql-hackers
about designing a better rule for when to fire the RI actions. Check
the list archives for details.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, alexander.stanier@egsgroup.com
Subject: Re: Referential Integrity
In-Reply-To: <5191.1044889792@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox