pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Youngblood <YoungblG@houstoncellular.com>
To: 'PostgreSQL General List' <pgsql-general@postgresql.org>
To: 'PostgreSQL SQL List' <pgsql-sql@postgresql.org>
To: 'PostgreSQL Novice List' <pgsql-novice@postgresql.org>
Subject: HELP: Problems with triggers and sql functions
Date: Thu, 31 Dec 1998 13:33:54 -0600
Message-ID: <2B9713A99045D211BB4E0008C75668692EA792@SCORPEXC1.houstoncell.com> (raw)

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
 
 




Message-ID: <2B9713A99045D211BB4E0008C75668692EA792@SCORPEXC1.houstoncell.com>
Permalink:  ../2B9713A99045D211BB4E0008C75668692EA792@SCORPEXC1.houstoncell.com/
Also on:    postgresql.org/message-id/2B9713A99045D211BB4E0008C75668692EA792@SCORPEXC1.houstoncell.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: YoungblG@houstoncellular.com, pgsql-general@postgresql.org, pgsql-novice@postgresql.org
  Subject: Re: HELP: Problems with triggers and sql functions
  In-Reply-To: <2B9713A99045D211BB4E0008C75668692EA792@SCORPEXC1.houstoncell.com>
  References: <2B9713A99045D211BB4E0008C75668692EA792@SCORPEXC1.houstoncell.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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