Message-ID: <3EC2B604.6090308@joeconway.com>
Date: Wed, 14 May 2003 14:32:52 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: Larry Rosenman <ler@lerctr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: PL/pgSQL: Logging Trigger. Advice/comments/other?
References: <382520000.1052942661@lerlaptop-red.iadfw.net>
In-Reply-To: <382520000.1052942661@lerlaptop-red.iadfw.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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