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'' or TG_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