Postgresql – Postgres – syntax error near IF when using inside Execute

postgresqlsyntaxtrigger

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?

Best Answer

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;
Related Question