From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Gavin" <gavin@grabias.com>, pgsql-sql@postgresql.org
Subject: Re: Function To Log Changes
Date: Mon, 5 Apr 2004 10:18:26 -0700
User-Agent: KMail/1.5.4
References: <50616.63.65.2.36.1081184499.squirrel@www.grabias.com>
In-Reply-To: <50616.63.65.2.36.1081184499.squirrel@www.grabias.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200404051018.26429.josh@agliodbs.com>

Gavin,

> 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?

It could be done, but would be extremely slow and awkward using current PL/
pgsql syntax.    You'd have to query the system tables for a list of columns, 
and then execute a series of dynamic queries.

I recommend instead one of the following two approaches:

1) Simply log the whole row of each archived table and don't worry about 
logging the individual columns, or

2) Use PL/tcl, PL/Pyton, or C where you can select columnns by ordinal 
position or other dynamic factor.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco