Date: Tue, 6 Apr 2004 12:06:10 -0700
From: elein <elein@varlena.com>
To: Gavin <gavin@grabias.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Function To Log Changes
Message-ID: <20040406120610.F31004@cookie.varlena.com>
Mail-Followup-To: Gavin <gavin@grabias.com>, pgsql-sql@postgresql.org
References: <50616.63.65.2.36.1081184499.squirrel@www.grabias.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <50616.63.65.2.36.1081184499.squirrel@www.grabias.com>
User-Agent: Mutt/1.3.22.1i

A plpython solution is available in Issue #66 of PostgreSQL GeneralBits.
http://www.varlena.com/GeneralBits/66

Let me know if this helps.

elein

On Mon, Apr 05, 2004 at 01:01:39PM -0400, Gavin wrote:
> 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();
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings