0

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?

  • 3
    EXECUTE statement executes the pure SQL statement/script. IF is not SQL but PL/PgSQL feature. – Abelisto Aug 12 '16 at 21:26
  • @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. – oddRaven Aug 12 '16 at 21:34
  • 1
    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 – Abelisto Aug 12 '16 at 21:54
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.