--- Jon Poulton <[EMAIL PROTECTED]> 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 > > [EMAIL PROTECTED] > > > > ---------------------------(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/ ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
