From: Gavin <gavin@grabias.com>
To: pgsql-sql@postgresql.org
Subject: Function To Log Changes
Date: Mon, 5 Apr 2004 13:01:39 -0400 (EDT)
Message-ID: <50616.63.65.2.36.1081184499.squirrel@www.grabias.com> (raw)
Hi All, I have been tinkering with a function to log the changes made on
any column through a function and trigger. However, I cant think of a way
to make this work through pl/pgsql. Any one have any ideas, or is it just
not possible?
SNIP
create or replace function logchange2() returns OPAQUE as '
DECLARE
columnname record;
c2 VARCHAR(64);
BEGIN
/* Cycle through the column names so we can find the changes being made */
FOR columnname IN SELECT attname FROM pg_attribute, pg_type
WHERE attnum > 0 AND typrelid=attrelid AND typname=''SOMETABLE'' LOOP
c2 := CAST(columnname.attname AS VARCHAR(64));
/* here lies the problem. How would I make plpgsql see OLD.columnname in
a dynamic fashion. I know this wont work whats below, but I am just
trying to express what I am trying to do */
IF ''OLD.'' || c2 != ''NEW.'' || c2 THEN
/* IF CHANGED DO SOMETHING */
RAISE NOTICE ''Update on column %'', c2;
END IF;
END LOOP;
return NULL;
END;
'
LANGUAGE plpgsql;
create trigger logchange2 AFTER UPDATE on TABLENAME FOR EACH ROW EXECUTE
PROCEDURE logchange2();
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: gavin@grabias.com
Subject: Re: Function To Log Changes
In-Reply-To: <50616.63.65.2.36.1081184499.squirrel@www.grabias.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