Thread: 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

Gregory S. Youngblood wrote:

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

    Yepp - simple mistake :-)

>
> 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  ERROR is the last literal of the whole CREATE statement:
    'sql'.  Trigger functions cannot  be  defined  in  the  'sql'
    language. You must use either C or a procedural language like
    PL/pgSQL or PL/Tcl.

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

    The following RULE can do it for you:

    CREATE RULE ins_tab1 AS ON INSERT TO tab1 DO
        INSERT INTO tab2 SELECT DISTINCT tab2.l, new.a, new.c
        WHERE 0 = (select count(*) from tab2 where a = new.a);

    My question is if there could be duplicates for tab1.a or  if
    it will have a unique index later?

    If  it  will  become  unique,  you might be better off with a
    view:

    CREATE TABLE tab1 (a char(16), b varchar(250), c char(1));
    CREATE TABLE tab2_keys (l char(10));
    CREATE VIEW  tab2 AS SELECT t2.l, t1.a, t1.c as g
        FROM tab1 t1, tab2 t2;

    This has the advantage that you can  simply  add  entries  to
    tab2_keys  and  for  every  entry  in  tab1  the  combination
    tab2_keys.l,tab1.a will appear immediately in tab2. And  they
    will  also  change/disappear immediately if one of the others
    changes/disappears.

    Tell me if that isn't what  you  planned  and  I'll  help  to
    create real triggers in PL/pgSQL that do what you want.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #