Dynamic column name postgresql trigger

Question

I'm trying to create a dynamic audit trigger that reads a column_name from the information schema and inserts into an audit table which column has been changed. So far I have the following code:

     CREATE OR REPLACE FUNCTION dynamic_column_audit()
        RETURNS trigger
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE NOT LEAKPROOF AS $BODY$
        <<main_function>>
    
    declare
    table_name_tr text;
    table_schema_tr text;
    
    column_name_trigger text;
    old_column_name_trigger record;
    new_column_name_trigger record;
    begin
    -- variables
    table_name_tr := TG_TABLE_NAME;
    table_schema_tr := TG_TABLE_SCHEMA;
    
    <<loop_sql>>
        FOR column_name_trigger IN (SELECT c.column_name FROM information_schema.columns c WHERE c.table_schema = table_schema_tr and c.table_name = table_name_tr ORDER BY ordinal_position asc)
            LOOP
            
            RAISE INFO E'\n Column name: %, table_name: %', column_name_trigger , table_schema_tr||'.'||table_name_tr;
            IF (TG_OP = 'UPDATE') THEN
            
            INSERT INTO my_loggingtable (operation, table_schema, table_name, column_name, old_value, new_value) 
            VALUES (TG_OP, table_name_tr, table_schema_tr, column_name_trigger, OLD.||column_name_trigger, NEW.||column_name_trigger);
end if;
    END LOOP loop_sql;
    return new;
    end main_function;
    $BODY$;

I'm trying to see whether it's possible to do something like NEW. + column_name or OLD> + column_name from the loop rather than hardcode the column name value for each table.

The idea behind this, which it could not be possible is to do an audit trigger for around 20 tables without adding the name all the time.

Laurenz Albe · Accepted Answer · 2021-07-14 09:23:43Z

1

You can use a construct like this:

DECLARE
   newval text;
BEGIN
   EXECUTE format(
              'SELECT ($1::%I).%I',
              table_name,
              column_name
           )
      INTO newval
      USING NEW;
END;

answered Jul 14, 2021 at 9:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic column name postgresql trigger

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related