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!

Answer 1

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).