Re: Triggers, functions and column names: a poser

On Sat, 17 Nov 2001, Grant Table wrote:

>
> here's a nice trigger problem for the weekend ;-)
>
> Specifically, for certain tables on INSERT, UPDATE or DELETE I
> want to write the following to a seperate logging table:
> the name of the table (relation); the action performed;
> the primary key of the row affected; and a timestamp.
>
> Rather than create a seperate RULE for each action on
> each table (pain to maintain) I would like to create a
> PL/PgSQL function to be called by triggers for the relevant tables.

Hi,

I had just written one such beast recently which in addition to
what you want also writes the values of the updated/inserted fields as a
serialised string. I wrote this primarily to replicate two databases with
a perlscript that reads this "logtable" and then talks to a remote
database with an expect-send sequence. I am currently writing an article
describing this which I was planning to submit to the
techdocs.postgresql.org site. IAC here's the code. I have also included
the droptrigger utility which well .. drops the triggers should something
bad happen. Hope you find this useful.

-- Setuptriggers
drop function setuptriggers();
create function setuptriggers() returns int as '
declare
fbui text;
fbdel text;
tbui text;
tbdel text;
tresult record;
cresult record;
pkeyname name;
typename name;
dropname name;
dummy record;
begin
for tresult in select * from pg_class
where relkind = ''r''
and relname !~ ''^pg_''
and relname !~ ''^Inv''
and relname !~ ''^pga_''
order by relname
loop
select into pkeyname c.attname from pg_class a, pg_index b, pg_attribute c
where a.relname = tresult.relname and
a.oid = b.indrelid and
a.oid = c.attrelid and
b.indkey[0] = c.attnum and
b.indisprimary=''t'';
if pkeyname is not null and tresult.relname != ''logtable'' and tresult.relname !~ ''^web_'' then
fbui := ''
create function logui_'' || quote_ident(tresult.relname) || ''() returns opaque as ''''
declare
serialized text;
currtime timestamp;
separator text;
op integer;
begin
currtime := ''''''''now'''''''';
separator := chr(178);
if TG_OP = ''''''''INSERT'''''''' then
op := 1;
else if TG_OP = ''''''''UPDATE'''''''' then
op := 2;
end if;
end if;
serialized := '''''''''''''''';
'';
for cresult in select * from pg_class a, pg_attribute b
where a.relname = tresult.relname and
a.oid = b.attrelid and
b.attnum > 0
order by b.attnum
loop
select into typename aa.typname from pg_type aa, pg_attribute bb, pg_class cc
where bb.attname = cresult.attname and
bb.atttypid = aa.oid and
bb.attrelid = cc.oid and
cc.relname = tresult.relname;
if typename !~ ''^bool'' then
fbui := fbui || '' if NEW.'' || quote_ident(cresult.attname) || '' is not null then
serialized := serialized || separator || '''''''''' || quote_ident(cresult.attname) || ''='''''''' || NEW.'' || quote_ident(cresult.attname) || '';
end if;
'';
else
fbui := fbui || '' if NEW.'' || quote_ident(cresult.attname) || '' is not null then
serialized := serialized || separator || '''''''''' || quote_ident(cresult.attname) || ''='''''''' || case when NEW.'' || quote_ident(cresult.attname) || '' then ''''''''TRUE'''''''' else ''''''''FALSE'''''''' end;
end if;
'';
end if;
end loop;
fbui := fbui || '' insert into logtable (keyid, tablename, pkeyname, value, updatetime, status) values (NEW.''|| quote_ident(pkeyname) || '', '''''''''' || quote_ident(tresult.relname) || '''''''''', '''''''''' || quote_ident(pkeyname) || '''''''''', serialized, currtime, op);
return new;
end;''''
language ''''plpgsql'''';'';
dropname := ''logui_'' || tresult.relname;
select into dummy * from pg_proc where proname = dropname and pronargs = 0;
if found then
fbui := ''drop function '' || quote_ident(dropname) || ''();
'' || fbui;
end if;
execute fbui;
fbdel := ''
create function logdel_'' || quote_ident(tresult.relname) || ''() returns opaque as ''''
declare
currtime timestamp;
begin
currtime := ''''''''now'''''''';
insert into logtable (keyid, tablename, pkeyname, value, updatetime, status) values (OLD.''|| quote_ident(pkeyname) || '', '''''''''' || quote_ident(tresult.relname) || '''''''''', '''''''''' || quote_ident(pkeyname) || '''''''''', NULL, currtime, 3);
return old;
end;''''
language ''''plpgsql'''';'';
dropname := ''logdel_'' || tresult.relname;
select into dummy * from pg_proc where proname = dropname and pronargs = 0;
if found then
fbdel := ''drop function '' || quote_ident(dropname) || ''();
'' || fbdel;
end if;
execute fbdel;
tbui := ''create trigger fui_'' || quote_ident(tresult.relname) || '' before insert or update on '' || quote_ident(tresult.relname) || ''
for each row execute procedure logui_'' || quote_ident(tresult.relname) || ''();'';
dropname := ''fui_'' || tresult.relname;
select into dummy * from pg_trigger where tgname = dropname;
if found then
tbui := ''drop trigger '' || quote_ident(dropname) || '' on '' || quote_ident(tresult.relname) || '';
'' || tbui;
end if;
execute tbui;
tbdel := ''create trigger fd_'' || quote_ident(tresult.relname) || '' before delete on '' || quote_ident(tresult.relname) || ''
for each row execute procedure logdel_'' || quote_ident(tresult.relname) || ''();'';
dropname := ''fd_'' || tresult.relname;
select into dummy * from pg_trigger where tgname = dropname;
if found then
tbdel := ''drop trigger '' || quote_ident(dropname) || '' on '' || quote_ident(tresult.relname) || '';
'' || tbdel;
end if;
execute tbdel;
end if;
end loop;
return 1;
end;'
language 'plpgsql';

-- Drop triggers

drop function droptriggers();
create function droptriggers() returns int as '
declare
tresult record;
dropname name;
dropcommand text;
dummy record;
begin
for tresult in select * from pg_class
where relkind = ''r''
and relname !~ ''^pg_''
and relname !~ ''^Inv''
and relname !~ ''^pga_''
order by relname
loop
dropname := ''logui_'' || tresult.relname;
select into dummy * from pg_proc where proname = dropname and pronargs = 0;
if found then
dropcommand := ''drop function '' || quote_ident(dropname) || ''()'';
raise notice ''Executing %'',dropcommand;
execute dropcommand;
end if;
dropname := ''logdel_'' || tresult.relname;
select into dummy * from pg_proc where proname = dropname and pronargs = 0;
if found then
dropcommand := ''drop function '' || quote_ident(dropname) || ''()'';
raise notice ''Executing %'',dropcommand;
execute dropcommand;
end if;
dropname := ''fui_'' || tresult.relname;
select into dummy * from pg_trigger where tgname = dropname;
if found then
dropcommand := ''drop trigger '' || quote_ident(dropname) || '' on '' || quote_ident(tresult.relname);
raise notice ''Executing %'',dropcommand;
execute dropcommand;
end if;
dropname := ''fd_'' || tresult.relname;
select into dummy * from pg_trigger where tgname = dropname;
if found then
dropcommand := ''drop trigger '' || quote_ident(dropname) || '' on '' || quote_ident(tresult.relname);
raise notice ''Executing %'',dropcommand;
execute dropcommand;
end if;
end loop;
return 1;
end;'
language 'plpgsql';