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';