X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2B940475956 for ; Mon, 10 Feb 2003 10:10:12 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 8C7AC47590C for ; Mon, 10 Feb 2003 10:10:11 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1AF9q5u005192; Mon, 10 Feb 2003 10:09:52 -0500 (EST) To: "Alexander Stanier" Cc: pgsql-sql@postgresql.org Subject: Re: Referential Integrity In-reply-to: References: Comments: In-reply-to "Alexander Stanier" message dated "Mon, 10 Feb 2003 12:17:21 +0000" Date: Mon, 10 Feb 2003 10:09:52 -0500 Message-ID: <5191.1044889792@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/192 X-Sequence-Number: 11590 "Alexander Stanier" 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