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
