pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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