On Wed, 18 Sep 2002, Thirumoorthy Bhuvneswari wrote:
> hi,
> I am using postgresql-7.0 with RedHat Linux-7.1. I am
> having two tables:tableA and tableB. On every insert
> in tableA there should be an update in tableB.
> I created a function like the following:
> CREATE FUNCTION test_trigger() RETURNS opaque As '
> DECLARE
> v_sno INT:=0;
> BEGIN
> FOR C1 IN select sno from tableB LOOP
> v_sno:=v_sno+c1.sno;
> update tableB set sno=v_sno;
> END LOOP;
> RETURN opaque;
You should be returning a variable, probably NEW if
you want the insert to go through or NULL if you don't.
In addition, unless I'm missing something your function
appears to update every row of tableB once for each
row of tableB (ie, you're doing n^2 row modifications
given n rows in tableB).