Hello
I am using in a project the audit trigger from the wiki
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
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
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
