Sorry for the delay This is the one I used https://wiki.postgresql.org/wiki/Audit_trigger
And the cfg_global_audit Column | Type | Modifiers ------------------+--------------------------+------------------------ audit_id | bigint | not null table_name | text | not null show_id | bigint | user_name | character varying(100) | action_timestamp | timestamp with time zone | not null default now() action | text | not null query | text | pflag | boolean | pjob | bigint | old_values | hstore | new_values | hstore | I have OLD and NEW that is true I went this way select hstore(new.*)->(select * from (select skeys(hstore(new.*)) ) as cfg_skeys where skeys like ('%_add_by%')) into skeys_add_by; insert into cfg_global_audit values (audit_tx_id, tg_table_name::text, new.show_id, skeys_add_by, current_timestamp, 'U', current_query(),'N', 0,hstore(old.*), hstore(new.*)); See the problem is that I might have group_add_by as a key, but I can also have instead of group_add_by, item_add_by if I would have a function that I could search in an store buy a key name pattern matching , aka get me the value for a key like ‘%add_by%’ it would be great In this case I could go around like it I will post another question related to hstore search since although it has a common ground it is different regarding the goal Thanks Armand On Mar 23, 2017, at 6:38 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 03/23/2017 02:00 PM, Armand Pirvu (home) wrote: > > Try number two. > >> Hello >> >> I am using in a project the audit trigger from the wiki > > Witch one? > (It seems the other spelling is banned) > >> One particular aspect is that not all tables that I am after have the same >> columns . >> And it is one in particular I am stumbling onto , and it ends i n _add_by. >> It can be group_add_by, car_add_by and so on. >> >> The old value of this column I need to record in the case of an update for >> example. The bad thing is also that it's position is not always the same. >> Meaning it can be 4th , 2nd and so on >> >> Just trying to avoid to have a function for each table and a bunch of hard >> coding which would be less than ideal > > So in the below what is the table schema for cfg_global_audit? > >> >> >> >> drop function func_global_audit(); >> create or replace function func_global_audit() returns trigger as $$ >> declare >> audit_tx_id bigint; >> begin >> select nextval('seq_aud_hist_prev'::regclass) into audit_tx_id; >> if tg_op = 'UPDATE' then >> insert into cfg_global_audit >> values (audit_tx_id, tg_table_name::text, current_user::text, >> current_timestamp, 'U', current_query(),'N', hstore(old.*), hstore(new.*), >> akeys(hstore(new.*) - hstore(old.*))); >> return new; >> end if; >> end; >> $$ >> language plpgsql security definer; >> >> drop trigger trig_cfg_group on cfg_group; >> create trigger trig_cfg_group after insert or update or delete on cfg_group >> for each row execute procedure func_global_audit(); >> >> >> >> levregdb=# select old_values from cfg_global_audit; >> >> old_values >> >> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> --------------------------------- >> "show_id"=>"1", "group_id"=>"33", "group_name"=>"Region 1", >> "group_note"=>"test1", "group_type"=>"Files", "group_add_by"=>"557651", >> "group_add_date"=>"2016-09-28 09:52:47.672398-05", "additional_info >> "=>NULL, "group_description"=>"" >> (1 row) >> >> So the idea is that I need 557651 which is the group_add_by old value >> recorded in user_name of the audit table > > Don't you have the OLD values in wherever you put hstore(OLD.*)? > > You have 557651, so I am not sure what you mean by you need it? > >> >> 1- is there any way I can look in the OLD record for such column something >> like OLD.%add_by% ? >> 2 - I was thinking also getting the column name which is easy and prepend >> with old. string and then do a select old.group_add_by into myvar. >> Apparently it does not work >> >> I mean yes I can do >> select 'old.'||column_name from information_schema.columns where table_name >> = 'cfg_group' and column_name like '%_add_by%' ; >> >> But >> create or replace function func_global_audit() returns trigger as $$ >> declare >> audit_tx_id bigint; >> cfg_schema text; >> cfg_by_col text; >> cfg_by_col1 text; >> begin >> select current_schema into cfg_schema; >> select nextval('seq_aud_hist_prev'::regclass) into audit_tx_id; >> if tg_op = 'UPDATE' then >> execute 'select COLUMN_NAME::text FROM INFORMATION_SCHEMA.COLUMNS WHERE >> table_schema='||quote_literal(cfg_schema)||' and >> table_name='||quote_literal(tg_table_name)||' and column_name like >> '||quote_literal('%_add_by%') into cfg_by_col; >> cfg_by_col1 := 'old.'||cfg_by_col; >> raise notice '%', cfg_by_col1; >> insert into cfg_global_audit values (audit_tx_id, >> tg_table_name::text, cfg_by_col1::text, current_timestamp, 'U', >> current_query(),'N', hstore(old.*), hstore(new.*), akeys(hstore(new.*) - >> hstore(old.*))); >> return new; >> end if; >> end; >> $$ >> language plpgsql security definer; >> >> drop trigger trig_cfg_group on cfg_group; >> create trigger trig_cfg_group after insert or update or delete on cfg_group >> for each row execute procedure func_global_audit(); >> >> >> And I get old.group_add_by instead of 557651 >> >> 3 - I tried >> >> >> drop function func_global_audit(); >> create or replace function func_global_audit() returns trigger as $$ >> declare >> audit_tx_id bigint; >> cfg_schema text; >> cfg_by_col text; >> cfg_by_col1 text; >> begin >> select current_schema into cfg_schema; >> select nextval('seq_aud_hist_prev'::regclass) into audit_tx_id; >> if tg_op = 'UPDATE' then >> execute 'select COLUMN_NAME::text FROM INFORMATION_SCHEMA.COLUMNS WHERE >> table_schema='||quote_literal(cfg_schema)||' and >> table_name='||quote_literal(tg_table_name)||' and column_name like >> '||quote_literal('%_add_by%') into cfg_by_col; >> cfg_by_col1 := 'old.'||cfg_by_col; >> raise notice '%', cfg_by_col1; >> execute 'insert into cfg_global_audit1 select $1' using >> cfg_by_col1; >> return new; >> end if; >> end; >> $$ >> language plpgsql security definer; >> >> >> >> And the same thing >> >> >> Can someone point me what am I doing wrong ? >> >> >> Thks >> >> >> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com