Re: PL/pgSQL: Logging Trigger. Advice/comments/other?

Larry Rosenman wrote:
> Ok, I finally finished this damn trigger to log changes to ONE frapping 
> table.
> 
> I'm wondering if any of you PL/pgSQL guru's out there see any better way 
> to do
> this:

I haven't had the time to follow this thread, but depending on your 
needs, maybe this will help (uses dblink_current_query() from 
contrib/dblink):


create table networks_log( id serial, ts timestamp default now(), sql text
);

create or replace function test_trig() returns trigger as '
declare sqltext text;
begin select into sqltext dblink_current_query(); insert into networks_log(sql) values (sqltext); if TG_OP = ''INSERT''
orTG_OP = ''UPDATE'' then  return new; else  return old; end if;
 
end;
' language 'plpgsql';

create table networks(id serial, interface text);

create trigger networks_trig
after update or insert or delete on networks
for each row execute procedure test_trig();

insert into networks(interface) values ('eth0');
insert into networks(interface) values ('eth1');
update networks set interface = 'eth3' where id = 2;
delete from networks where id = 1;

regression=# select * from networks; id | interface
----+-----------  2 | eth3
(1 row)

regression=# select ts::time, sql from networks_log;       ts        |                         sql
-----------------+------------------------------------------------------ 13:49:29.395334 | insert into
networks(interface)values ('eth0'); 13:49:34.818366 | insert into networks(interface) values ('eth1'); 13:49:39.607128
|update networks set interface = 'eth3' where id = 2; 13:49:42.797973 | delete from networks where id = 1;
 
(4 rows)


HTH,

Joe