Postgres - syntax error near IF when using inside Execute

Question

I am using an after update trigger in order recognize and log any changes made to records in the 'tasks' table. The column, and new and old value are all dynamic, so different every time.

This is what the trigger function looks like right now:

CREATE OR REPLACE FUNCTION fn_tasks_after_update()
RETURNS trigger
AS $BODY$ 
DECLARE 
 ri RECORD;
 col_name TEXT;
BEGIN 
 FOR ri IN
 SELECT column_name FROM information_schema.columns WHERE table_name = 'tasks'
 LOOP
 EXECUTE
 'IF 1ドル.' || ri.column_name || ' <> 2ドル.' || ri.column_name || ' THEN
 INSERT INTO tasks_log (task_id, type, "column", old_value, new_value) 
 VALUES (1ドル.id, $$update$,ドル 3,ドル 1ドル.' || ri.column_name || ', 2ドル.' || ri.column_name || ');
 END IF;'
 USING NEW, OLD, ri.column_name;
 END LOOP;
 RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fn_tasks_after_update()
OWNER TO postgres;

The trigger worked before adding the IF, and the syntax does not seem any different from what is in the 'INSERT INTO tasks_log' block.

This does work:

EXECUTE
 'INSERT INTO tasks_log (task_id, type, "column", old_value, new_value) 
 VALUES (1ドル.id, $$update$,ドル 3,ドル 1ドル.' || ri.column_name || ', 2ドル.' || ri.column_name || ');'

This does not work:

EXECUTE
 'IF 1ドル.' || ri.column_name || ' <> 2ドル.' || ri.column_name || ' THEN
 INSERT INTO tasks_log (task_id, type, "column", old_value, new_value) 
 VALUES (1ドル.id, $$update$,ドル 3,ドル 1ドル.' || ri.column_name || ', 2ドル.' || ri.column_name || ');
 END IF;' 

What is going wrong?

• Abelisto: EXECUTE statement executes the pure SQL statement/script. IF is not SQL but PL/PgSQL feature.
• oddRaven: @Abelisto Is it possible to use the IF in the same function somehow or do I need to create another and call that one with EXECUTE? Assuming I could do that.
• Abelisto: You can execute 'select <check_condition>' into boolean_var and then use the boolean_var in the regular if. However there are a lot articles about the logging of the data changes. For example: Audit trigger - here you can find the good example how to get only changed fields using the hstore extension

Answer 1

This is the solution thanks to Abelisto's comment:

CREATE OR REPLACE FUNCTION fn_tasks_after_update()
RETURNS trigger
AS $BODY$ 
DECLARE 
 ri RECORD;
 is_changed BOOLEAN;
BEGIN 
 FOR ri IN
 SELECT column_name FROM information_schema.columns WHERE table_name = 'tasks'
 LOOP
 EXECUTE 'SELECT 1ドル.' || ri.column_name || ' <> 2ドル.' || ri.column_name
 USING NEW, OLD
 INTO is_changed;
 IF is_changed THEN
 EXECUTE
 'INSERT INTO tasks_log (task_id, type, "column", old_value, new_value) 
 VALUES (1ドル.id, $$update$,ドル 3,ドル 1ドル.' || ri.column_name || ', 2ドル.' || ri.column_name || ');'
 USING NEW, OLD, ri.column_name;
 END IF;
 END LOOP;
 RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fn_tasks_after_update()
OWNER TO postgres;