[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] after delete trigger behavior
From:       "Russell Simpkins" <russellsimpkins () hotmail ! com>
Date:       2005-06-23 13:15:41
Message-ID: BAY103-F398954E58D63E29F4BBDD5B5EA0 () phx ! gbl
[Download RAW message or body]

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;



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
[prev in list] [next in list] [prev in thread] [next in thread]