CODEWITHSUNDEEP
sqlpostgresqltriggers
Mariano P.
Mariano P.

Reputation: 23

Dynamic column name postgresql trigger

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.

Upvotes: 2

Views: 163

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246533

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;

Upvotes: 1

Related Questions