Message-ID: <50616.63.65.2.36.1081184499.squirrel@www.grabias.com>
Date: Mon, 5 Apr 2004 13:01:39 -0400 (EDT)
Subject: Function To Log Changes
From: "Gavin" <gavin@grabias.com>
To: pgsql-sql@postgresql.org
User-Agent: SquirrelMail/1.4.2
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal

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();