From: Rajesh Kumar Mallah. <mallah@trade-india.com>
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
Message-ID: <200207161655.14794.mallah@trade-india.com> (raw)
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.
Attachments:test_case.sql (text/x-diff, 1.0K) download-- 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();
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: mallah@trade-india.com
Subject: Re: Cascading deletions does not seem to work inside PL/PGSQL functions.
In-Reply-To: <200207161655.14794.mallah@trade-india.com>
* 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