Postgresql Syntax Error in Trigger Function

Question

I want to create the following trigger function in my Postgresql DB :

CREATE FUNCTION attribute_edit_history() 
    RETURNS TRIGGER AS 
$BODY$   
  BEGIN
    Select 
            CASE 
            WHEN NOT EXISTS
            (SELECT * FROM public."TB02_MDD_KEY" where "ENCODED_ID" =ENCODE(CONVERT_TO(NEW."ATTRIBUTE_NAME", 'UTF-8'), 'base64'))
            THEN 
             CASE
             WHEN OLD."ATTRIBUTE_NAME" is distinct from NEW."ATTRIBUTE_NAME"
                THEN
                     INSERT INTO public."TB08_ATTRIBUTE_EDIT_HISTORY"(
                     "ENCODED_ID_OLD","ENCODED_ID_NEW" , "VERSION", "ATTRIBUTE_OLD", "ATTRIBUTE_NEW", "ATTRIBUTE_NEW_ID")
                     VALUES ( OLD."ENCODED_ID", NEW."ENCODED_ID", NEW."VERSION", OLD."ATTRIBUTE_NAME", NEW."ATTRIBUTE_NAME", ENCODE(CONVERT_TO(NEW."ATTRIBUTE_NAME", 'UTF-8'), 'base64')); 
                END;
             END;

    RETURN NEW;

  END;

$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER "attribute_edit_history" BEFORE UPDATE ON "TB02_MDD_KEY"
  FOR EACH ROW EXECUTE PROCEDURE attribute_edit_history();

I am getting the following syntax error :

ERROR:  syntax error at or near "INTO"
LINE 13:       INSERT INTO public."TB08_ATTRIBUTE_EDIT_HISTORY"(
                      ^
SQL state: 42601
Character: 352

I dont know where I am going wrong.

Thanks in advance!

GMB · Accepted Answer · 2020-05-25 01:36:51Z

Most likely, you want a simple IF statement rather than nested CASE expressions:

CREATE FUNCTION attribute_edit_history() 
RETURNS TRIGGER AS 
$BODY$   
BEGIN
    IF 
        NOT EXISTS(
            SELECT 1 
             FROM public.TB02_MDD_KEY 
             WHERE ENCODED_ID = ENCODE(CONVERT_TO(NEW.ATTRIBUTE_NAME, 'UTF-8'), 'base64')
        ) 
        AND OLD.ATTRIBUTE_NAME IS DISTINCT FROM NEW.ATTRIBUTE_NAME
    THEN
        INSERT INTO public.TB08_ATTRIBUTE_EDIT_HISTORY(
            ENCODED_ID_OLD,
            ENCODED_ID_NEW , 
            VERSION, 
            ATTRIBUTE_OLD, 
            ATTRIBUTE_NEW, 
            ATTRIBUTE_NEW_ID
        ) VALUES ( 
            OLD.ENCODED_ID, 
            NEW.ENCODED_ID, 
            NEW.VERSION, 
            OLD.ATTRIBUTE_NAME, 
            NEW.ATTRIBUTE_NAME, 
            ENCODE(CONVERT_TO(NEW.ATTRIBUTE_NAME, 'UTF-8'), 'base64')
        ); 
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

Note that I removed the double quotes around the tables and columns identifiers; you don't need those (unless you have case-sensitive identifiers, which does not seem to be the case here).

Collectives™ on Stack Overflow

Postgresql Syntax Error in Trigger Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related