Re: [GENERAL] audit function and old.column - Mailing list pgsql-general

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
positionis 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_byinto 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)||'
andtable_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.klaver@aklaver.com