From: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Organization: Infocom Network Limited.
To: pgsql-sql@postgresql.org
Subject: Cascading deletions does not seem to work inside PL/PGSQL functions.
Date: Tue, 16 Jul 2002 16:55:14 +0530
User-Agent: KMail/1.4.1
MIME-Version: 1.0
Content-Type: Multipart/Mixed;
	boundary="------------Boundary-00=_2EAC3CX00SOY7ZENQBB2"
Message-Id: <200207161655.14794.mallah@trade-india.com>

--------------Boundary-00=_2EAC3CX00SOY7ZENQBB2
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi folks,

This problem has been troubling me for quite sometime and=20
I would be very thankful for your help.=20

I have included the complete commented script to recreate the problem in
question.

The problem is inside a plpgsql function i do not see the records in the sl=
ave tables
getting deleted when i delete the corresponing referenced record from the m=
aster table.

But things as expected  inside a Transaction at  the PSQL prompt.


any help is very much appreciated.

regds
mallah.


-------------------- stuff in test_case.sql -----------------------
--  W A R N I N G=20
--- scripts will delete tables t_master and t_slave and a function t_test()
---

DROP TABLE t_master; --clean up stuff first.
DROP TABLE t_slave;


CREATE  TABLE t_master (id integer primary key);
CREATE  TABLE t_slave  (id integer references t_master=20

				on delete cascade=20
				on update cascade unique
=09=09=09=09
				);

INSERT  INTO t_master values (1);
INSERT  INTO t_slave values (1);

-- Demonstrate that record in salve table do get=20
-- deleted when the master record is deleted.

BEGIN work;
delete from t_master where id=3D1;
select id from t_slave where id=3D1;  --  <-- this selects returns no recor=
d.
ROLLBACK;

-- Same stuff tried inside a PL/PGSQL function...

DROP FUNCTION t_test();
CREATE OR REPLACE FUNCTION  t_test () RETURNS integer AS '
DECLARE rec RECORD;
BEGIN
        DELETE FROM t_master where id=3D1;
        SELECT INTO rec id from t_slave where id=3D1; -- <-- this selects r=
eturns record.=20
        RAISE NOTICE ''id in slave table is %'' , rec.id ;=20
        RETURN 1;
END;

' LANGUAGE 'plpgsql' ;

select t_test();


--------------------------- the end -----------------------------------


--=20
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.


--------------Boundary-00=_2EAC3CX00SOY7ZENQBB2
Content-Type: text/x-diff;
  charset="us-ascii";
  name="test_case.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="test_case.sql"

--  W A R N I N G 
--- scripts will delete tables t_master and t_slave and a function t_test()
---

DROP TABLE t_master; --clean up stuff first.
DROP TABLE t_slave;


CREATE  TABLE t_master (id integer primary key);
CREATE  TABLE t_slave  (id integer references t_master 

				on delete cascade 
				on update cascade unique
				
				);

INSERT  INTO t_master values (1);
INSERT  INTO t_slave values (1);

-- Demonstrate that record in salve table do get 
-- deleted when the master record is deleted.

BEGIN work;
delete from t_master where id=1;
select id from t_slave where id=1;  --  <-- this selects returns no record.
ROLLBACK;

-- Same stuff tried inside a PL/PGSQL function...

DROP FUNCTION t_test();
CREATE OR REPLACE FUNCTION  t_test () RETURNS integer AS '
DECLARE rec RECORD;
BEGIN
        DELETE FROM t_master where id=1;
        SELECT INTO rec id from t_slave where id=1; -- <-- this selects returns record. 
        RAISE NOTICE ''id in slave table is %'' , rec.id ; 
        RETURN 1;
END;

' LANGUAGE 'plpgsql' ;

select t_test();

--------------Boundary-00=_2EAC3CX00SOY7ZENQBB2--