Thread: Doubt on opaque returntype!!

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;
END;
'LANGUAGE 'plpgsql';

This function is created. After that I created a
trigger as the following:
createm trigger test_trigger_1 after insert on tableA
for each row execute procedure test_trigger();

This trigger is also created. But, when I try to
insert into the tableA it comes with the error:

'ERROR during compile of test_trigger near line 8
ERROR:return type mismatch in function returning table
row at or near "opaque"

I don't know where I am wrong. please help me.
thanks.
bhuvaneswari.t


__________________________________________________
Do you Yahoo!?
Yahoo! News - Today's headlines
http://news.yahoo.com

On Wed, 2002-09-18 at 12:01, Thirumoorthy Bhuvneswari wrote:

> This trigger is also created. But, when I try to
> insert into the tableA it comes with the error:
>
> 'ERROR during compile of test_trigger near line 8
> ERROR:return type mismatch in function returning table
> row at or near "opaque"

I think your function should return NULL.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Give, and it shall be given unto you; good measure,
      pressed down, and shaken together, and running over,
      shall men pour into your lap. For by your standard of
      measure it will be measured to in return."
                                           Luke 6:38

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).