postgresql - Get values from varying columns in a generic trigger -

postgresql - Get values from varying columns in a generic trigger -

i new postgresql , found trigger serves purpose except 1 little thing. trigger quite generic , runs across different tables , logs different field changes. found here.

what need test specific field changes tables alter on trigger fires. thought of using substr column have same name format e.g. xxx_cust_no xxx can alter 2 or 4 characters. need log value in thexxx_cust_no field every record written history_ / audit table. using bunch of if / else statements accomplish not do.

the trigger works logs table_name, column_name, old_value, new_value. need log xxx_cust_no of record changed well.

basically need dynamic sql dynamic column names. format helps format dml command. pass values new , old using clause.

given these tables:

create table tbl ( t_id serial primary key ,abc_cust_no text ); create table log ( id int ,table_name text ,column_name text ,old_value text ,new_value text );

it work this:

create or replace function trg_demo() returns trigger $func$ begin execute format(' insert log(id, table_name, column_name, old_value, new_value) select ($2).t_id , $3 , $4 ,($1).%1$i ,($2).%1$i', tg_argv[0]) using old, new, tg_relname, tg_argv[0]; homecoming new; end $func$ language plpgsql; create trigger demo before update on tbl each row execute procedure trg_demo('abc_cust_no'); -- col name here.

sql fiddle.

related reply on dba.se:

how access new or old field given field's name?

list of special variables visible in plpgsql trigger functions in manual.

postgresql triggers plpgsql dynamic-sql