X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CE16BD1B97B for ; Mon, 5 Apr 2004 22:34:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 14700-04 for ; Mon, 5 Apr 2004 19:34:36 -0300 (ADT) Received: from web20803.mail.yahoo.com (web20803.mail.yahoo.com [216.136.226.192]) by svr1.postgresql.org (Postfix) with SMTP id 27299D1D282 for ; Mon, 5 Apr 2004 19:34:02 -0300 (ADT) Message-ID: <20040405223442.3691.qmail@web20803.mail.yahoo.com> Received: from [24.248.12.72] by web20803.mail.yahoo.com via HTTP; Mon, 05 Apr 2004 15:34:42 PDT Date: Mon, 5 Apr 2004 15:34:42 -0700 (PDT) From: Jeff Eckermann Subject: Re: Question on pgsql trigger To: Jon Poulton , pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/28 X-Sequence-Number: 17403 --- Jon Poulton 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/