Re: audit function and old.column

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com