Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    London
    Posts
    2

    Unanswered: Problem with erroneous Function/Trigger in Postgres

    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.

  2. #2
    Join Date
    Mar 2004
    Location
    London
    Posts
    2

    Re: Problem with erroneous Function/Trigger in Postgres

    Forget it, I figured it out myself....

    Heres the working solution if anyone is ever interested:
    CREATE FUNCTION message_text_concat() RETURNS TRIGGER AS'
    DECLARE
    allText TEXT := '''';
    currentRecord RECORD;
    BEGIN

    IF (TG_WHEN = ''BEFORE'') THEN
    RAISE EXCEPTION ''Trigger function message_text_concat() should not be called before insert/update/delete'';
    END IF;

    IF(TG_LEVEL = ''STATEMENT'') THEN
    RAISE EXCEPTION ''Trigger function message_text_concat() should be called by a row level trigger only.'';
    END IF;

    IF (TG_OP = ''DELETE'') THEN
    DELETE FROM cks_messagetext WHERE id = OLD.id;
    RETURN OLD;

    ELSIF (TG_OP = ''UPDATE'') THEN

    FOR currentRecord IN SELECT * FROM cks_part WHERE typeid = ''1'' AND
    sourcemessageid = NEW.id LOOP
    allText := allText || '' '' || currentRecord.textdata;
    END LOOP;

    allText := allText || '' '' || NEW.subject;
    UPDATE cks_messagetext SET textdata = allText WHERE id = NEW.id;
    RETURN NEW;

    ELSIF (TG_OP = ''INSERT'') THEN

    FOR currentRecord IN SELECT * FROM cks_part WHERE typeid = ''1'' AND
    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;
    END IF;

    END;
    'LANGUAGE plpgsql;


    --Create the Trigger on the discussion message table to update cks_messagetext
    CREATE TRIGGER "trigger_cks_discussionmessage_text_update" AFTER UPDATE OR INSERT OR DELETE
    ON cks_discussionmessage FOR EACH ROW
    EXECUTE PROCEDURE message_text_concat();

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •