Re: [SQL] Triggers and sql functions not working...

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(at)debis(dot)com (Jan Wieck) #