Hi folks, This problem has been troubling me for quite sometime and I would be very thankful for your help.
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 slave tables getting deleted when i delete the corresponing referenced record from the master 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 --- 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(); --------------------------- the end ----------------------------------- -- 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.
-- 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();
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])