From: Joe Conway <mail@joeconway.com>
To: Larry Rosenman <ler@lerctr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: PL/pgSQL: Logging Trigger. Advice/comments/other?
Date: Wed, 14 May 2003 14:32:52 -0700
Message-ID: <3EC2B604.6090308@joeconway.com> (raw)
In-Reply-To: <382520000.1052942661@lerlaptop-red.iadfw.net>
References: <382520000.1052942661@lerlaptop-red.iadfw.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, ler@lerctr.org
Subject: Re: PL/pgSQL: Logging Trigger. Advice/comments/other?
In-Reply-To: <3EC2B604.6090308@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox