Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Larry Rosenman <ler@lerctr.org>
Subject: Re: trigger question
Date: Thu, 16 May 2002 11:05:48 -0700
Cc: pgsql-sql@postgresql.org
References: <1021570694.95676.7.camel@ler-freebie.iadfw.net>
	<200205161049.23148.josh@agliodbs.com>
	<1021571463.95676.11.camel@ler-freebie.iadfw.net>
In-Reply-To: <1021571463.95676.11.camel@ler-freebie.iadfw.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200205161105.48972.josh@agliodbs.com>


Larry,

> I want to log the changes to it in a trigger.  Occasionally we add fields=
 to=20
it, so=20
> I'd like the trigger to know about them automatically.  I need to log=20
old/new for just the fields
> that change (and the netblock, which is the primary key).=20

The problem is, there is no way for you to loop through all of the fields i=
n a=20
RECORD without knowing in advance what those fields are.  There is no=20
NEW.field(1) that I know of.  And RECORD data types are not accessable in=
=20
dynamic EXECUTE strings.=20=20

So, two approaches:
1. do all this in C and manipulate the Postgres data libraries directly,=20
allowing you to loop through the collection of fields in a table.  Don't as=
k=20
me how to do this; you'll have to take it up on PGSQL-HACKERS.

2. Save the entire old record (BEFORE update), and then the entire new reco=
rd=20
(AFTER update) to a history buffer table.  Use the pgsql system tables to=
=20
loop through the fields in the main table, logging each entry where the two=
=20
fields are different.=20=20

The two problems with approach #2 are: 1. you cannot reject updates this wa=
y,=20
and 2. it'll be dog slow.=20=20

The simpler solution is for you do decide on a permanent structure for your=
=20
table, neh?=20=20

--=20
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology 	josh@agliodbs.com
    and data management solutions 	(415) 565-7293
   for law firms, small businesses 	 fax 621-2533
    and non-profit organizations. 	San Francisco