pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Question on pgsql trigger 2+ messages / 2 participants [nested] [flat]
* Question on pgsql trigger @ 2004-03-29 17:12 Jon Poulton <jon@illumining.com> 2004-04-05 22:34 ` Re: Question on pgsql trigger Jeff Eckermann <jeff_eckermann@yahoo.com> 0 siblings, 1 reply; 2+ messages in thread From: Jon Poulton @ 2004-03-29 17:12 UTC (permalink / raw) To: pgsql-sql; +Cc: jon@illumining.com Hi there, Im having a go at writing my first set of triggers for postgres and Im having trouble with an error message which the trigger produces when it tries to compile/call the function Ive written in pgsql. The error message is: ERROR: syntax error at or near ";" CONTEXT: compile of PL/pgSQL function "text_update" near line 31 I cant find the error! As far as I can see the syntax is fine from whats in the manual. Can anyone see what the problem with the function below is: CREATE FUNCTION text_update() RETURNS TRIGGER AS' DECLARE allText TEXT; currentRecord RECORD; BEGIN IF TG_WHEN = BEFORE THEN RAISE EXCEPTION ''Trigger function text_update should not be called before INSERT/UPDATE/DELETE''; END IF; IF TG_LEVEL = STATEMENT THEN RAISE EXCEPTION ''Trigger function text_update should be called as a row level trigger''; END IF; IF TG_OP = DELETE THEN DELETE FROM cks_messagetext WHERE cks_messagetext.id = OLD.id; RETURN OLD; ELSIF TG_OP = UPDATE THEN FOR currentRecord IN SELECT textdata FROM cks_part WHERE cks_part.type = 1 AND cks_part.sourcemessageid = NEW.id LOOP allText := allText || '' '' || currentRecord.textdata; END LOOP; allText := allText || '' '' || NEW.subject; UPDATE cks_messagetext SET cks_messagetext.textdata = allText WHERE cks_messagetext.id = NEW.id; RETURN NEW; ELSIF TG_OP = INSERT THEN FOR currentRecord IN SELECT textdata FROM cks_part WHERE cks_part.type = 1 AND cks_part.sourcemessageid = NEW.id LOOP allText := allText || '' '' || currentRecord.textdata; END LOOP; allText := allText || '' '' || NEW.subject; INSERT INTO cks_messagetext (id, textdata) VALUES (NEW.id, allText); RETURN NEW; ENDIF; END; 'LANGUAGE plpgsql; Thank you for any help in advance. Jon Poulton jon@illumining.com ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Question on pgsql trigger 2004-03-29 17:12 Question on pgsql trigger Jon Poulton <jon@illumining.com> @ 2004-04-05 22:34 ` Jeff Eckermann <jeff_eckermann@yahoo.com> 0 siblings, 0 replies; 2+ messages in thread From: Jeff Eckermann @ 2004-04-05 22:34 UTC (permalink / raw) To: Jon Poulton <jon@illumining.com>; pgsql-sql --- Jon Poulton <jon@illumining.com> wrote: > Hi there, > Im having a go at writing my first set of triggers > for postgres and Im > having trouble with an error message which the > trigger produces when it > tries to compile/call the function Ive written in > pgsql. The error message > is: > > ERROR: syntax error at or near ";" > CONTEXT: compile of PL/pgSQL function "text_update" > near line 31 > > I cant find the error! As far as I can see the > syntax is fine from whats in > the manual. Can anyone see what the problem with the > function below is: > > > CREATE FUNCTION text_update() RETURNS TRIGGER AS' > DECLARE > allText TEXT; > currentRecord RECORD; > BEGIN > IF TG_WHEN = BEFORE THEN > RAISE EXCEPTION ''Trigger function text_update > should not be called before > INSERT/UPDATE/DELETE''; > END IF; > IF TG_LEVEL = STATEMENT THEN > RAISE EXCEPTION ''Trigger function text_update > should be called as a row > level trigger''; > END IF; > IF TG_OP = DELETE THEN > DELETE FROM cks_messagetext WHERE > cks_messagetext.id = OLD.id; > RETURN OLD; > ELSIF TG_OP = UPDATE THEN > FOR currentRecord IN SELECT textdata FROM cks_part > WHERE cks_part.type = 1 > AND > cks_part.sourcemessageid = NEW.id LOOP > allText := allText || '' '' || > currentRecord.textdata; > END LOOP; > allText := allText || '' '' || NEW.subject; > UPDATE cks_messagetext SET cks_messagetext.textdata > = allText WHERE > cks_messagetext.id = NEW.id; > RETURN NEW; > ELSIF TG_OP = INSERT THEN > FOR currentRecord IN SELECT textdata FROM cks_part > WHERE cks_part.type = 1 > AND > cks_part.sourcemessageid = NEW.id LOOP > allText := allText || '' '' || > currentRecord.textdata; > END LOOP; > allText := allText || '' '' || NEW.subject; > INSERT INTO cks_messagetext (id, textdata) VALUES > (NEW.id, allText); > RETURN NEW; > ENDIF; "END IF" is two words? > END; > 'LANGUAGE plpgsql; > > > Thank you for any help in advance. > > Jon Poulton > > jon@illumining.com > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend __________________________________ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-04-05 22:34 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-03-29 17:12 Question on pgsql trigger Jon Poulton <jon@illumining.com> 2004-04-05 22:34 ` Jeff Eckermann <jeff_eckermann@yahoo.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