Обсуждение: Triggers and sql functions not working...

I posted this over the holidays, but didn't see my post so I am trying again
in case there were problems with my mail server... My apologies if this is a
duplicate message.

I am having a problem getting a trigger created and I'm sure it is a
simple/stupid mistake, yet nothing I read, nor nothing I try, seems to let
me create this trigger.

Postgres 6.4 (from the 6.4.1 OOPS distribution) on Linux 2.0.35 SMP.

Sample table tab1:
            a            b            c
            xyz            stuff here    Y
            abc            more stuff    N

Sample table tab2:
            l            a            g
            name        xyz            Y
            aname        xyz            Y

where a is char(16); b is varchar(250); c is char(1); l is char(8); and, g
is char(1).

create function update_tab2 () returns int4 as 'insert into tab2 (l,a,g)
select distinct a.l,b.a,b.c from tab2 a,tab1 b where b.a not in (select
distinct a from tab2); select 1 as exitvalue;' language 'sql';

The function creates properly.

Here's the goal. If and when tab1 has a new value inserted into it, tab2
should be updated according to function update_tab2.

Using the above samples, if I run:
    select update_tab2();
I get:
    exitvalue
    ------------
               1

and then:        select * from tab2;
yields:
            l            a            g
            name        xyz            Y
            aname        xyz            Y
            name        abc            N
            aname        abc            N

which is correct.

So, I try this:

    create trigger new_tab1_a after insert on tab1 for each row execute
procedure update_tab2 ();

generates:

    ERROR:  CreateTrigger: function update_tab2 () does not exist.


I have also tried changing the function and trigger to pass an int4 value.

If I run the function manually, it all works, but I can't get the trigger to
create.

Does anyone have any ideas or advice? This is really frustrating.

Thanks
Greg


Gregory S. Youngblood
ext 2164

>>>>> "GY" == Greg Youngblood <YoungblG@houstoncellular.com> writes:

. . .

 GY> create function update_tab2 () returns int4 as 'insert into tab2 (l,a,g)
 GY> select distinct a.l,b.a,b.c from tab2 a,tab1 b where b.a not in (select
 GY> distinct a from tab2); select 1 as exitvalue;' language 'sql';

. . .

 GY> create trigger new_tab1_a after insert on tab1 for each row execute
 GY> procedure update_tab2 ();

 GY> generates:

 GY> ERROR:  CreateTrigger: function update_tab2 () does not exist.

Function for triggers must have signature 'xxx() returns opaque', but
not '... returns int'. Pehaps this is your mistake?

--
Anatoly K. Lasareff              Email:       tolik@icomm.ru
Senior programmer