From: | "Russell Simpkins" <russellsimpkins(at)hotmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: after delete trigger behavior |
Date: | 2005-06-23 13:15:41 |
Message-ID: | BAY103-F398954E58D63E29F4BBDD5B5EA0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
After delete worked and the a foreach execute update seems to work best.
Below is a satisfactory test set.
-- create test table
CREATE TABLE test1 (
a int,
b int,
c int);
-- create resort function
CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS '
DECLARE
eachrow RECORD;
innerrow RECORD;
sort INT := 0;
BEGIN
sort := 0;
FOR eachrow IN EXECUTE ''SELECT * FROM portfolio.test1 WHERE a = '' ||
OLD.a LOOP
IF eachrow.b != OLD.b THEN
EXECUTE ''UPDATE portfolio.test1 SET c = '' || sort || '' WHERE a
= '' || eachrow.a || '' AND b = '' || eachrow.b || '''';
sort := sort +1;
END IF;
END LOOP;
RETURN OLD;
END;
' language 'plpgsql';
-- create trigger
CREATE TRIGGER u_test1 AFTER DELETE ON portfolio.test1 FOR EACH ROW EXECUTE
PROCEDURE resort_test1();
-- sample data
insert into test1 values(1,1,0);
insert into test1 values(1,2,1);
insert into test1 values(1,3,2);
insert into test1 values(1,4,3);
insert into test1 values(1,5,4);
insert into test1 values(2,1,0);
insert into test1 values(2,2,1);
insert into test1 values(2,3,2);
insert into test1 values(2,4,3);
insert into test1 values(2,5,4);
-- test delete
delete from test1 where b = 2 or b = 4;
-- view test results
select * from test1 order by a, b, c;
From | Date | Subject | |
---|---|---|---|
Next Message | bumby | 2005-06-23 17:03:07 | Help with query, stuck :( |
Previous Message | orange_crush_068 | 2005-06-23 12:43:52 | Unique primary index? |