pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Triggers and sql functions not working...
2+ messages / 2 participants
[nested] [flat]

* Triggers and sql functions not working...
@ 1999-01-04 18:33 Greg Youngblood <YoungblG@houstoncellular.com>
  1999-01-05 15:41 ` Re: [SQL] Triggers and sql functions not working... Jan Wieck <jwieck@debis.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Greg Youngblood @ 1999-01-04 18:33 UTC (permalink / raw)
  To: pgsql-sql

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
 
 




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] Triggers and sql functions not working...
  1999-01-04 18:33 Triggers and sql functions not working... Greg Youngblood <YoungblG@houstoncellular.com>
@ 1999-01-05 15:41 ` Jan Wieck <jwieck@debis.com>
  0 siblings, 0 replies; 2+ messages in thread

From: jwieck@debis.com @ 1999-01-05 15:41 UTC (permalink / raw)
  To: YoungblG@houstoncellular.com; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-01-05 15:41 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-04 18:33 Triggers and sql functions not working... Greg Youngblood <YoungblG@houstoncellular.com>
1999-01-05 15:41 ` Jan Wieck <jwieck@debis.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox