Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.1/8.9.1) with SMTP id KAA07957 for ; Tue, 5 Jan 1999 10:22:57 -0500 (EST) (envelope-from wieck@sapserv.debis.de) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m0zxYc3-000EBRC; Tue, 5 Jan 99 16:41 MET Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Re: [SQL] Triggers and sql functions not working... To: YoungblG@houstoncellular.com (Greg Youngblood) Date: Tue, 5 Jan 1999 16:41:42 +0100 (MET) Cc: pgsql-sql@postgreSQL.org Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: <2B9713A99045D211BB4E0008C75668692EA7A2@SCORPEXC1.houstoncell.com> from "Greg Youngblood" at Jan 4, 99 12:33:18 pm X-Mailer: ELM [version 2.4 PL25] Content-Type: text 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) #