Skip site navigation (1) Skip section navigation (2)

field incrementing in a PL/pgSQL trigger

From: "Tim Vadnais" <tvadnais(at)earthlink(dot)net>
To: "Postgres General List" <pgsql-general(at)postgresql(dot)org>
Subject: field incrementing in a PL/pgSQL trigger
Date: 2004-10-28 16:14:17
Message-ID: GNECLEMNHALMMLOCGFHFAEOHCBAA.tvadnais@earthlink.net (view raw or flat)
Thread:
Lists: pgsql-general
Hi,

My boss wants to add a special type of logging to some of our tables on
update/delete/insert.  I need to log who, when, table_name, field name,
original value and new value for each record, but only logging modified
fields, and he wants me to do this using postgres pgSQL triggers.  The
changes would be inserted into a second table.

We are given 10 automatically created variables.  Some of which I know I can
use: NEW, OLD, TG_WHEN, TG_OP and TG_RELNAME.  I can use these to get
general information for the update, but when the trigger is called, I don't
know how many fields are in the tables that are being updated.

My questions are: Is there a way I can dynamically determine the number of
fields in the rows that is being maintained. (a function much like:
PQnfields(const PGresult *); )
Then I need a way to get the name of the field (using a function much like:
PQfname(const PGresult *, int); )

Using the dynamically generated name I could then walk the NEW and OLD
columns to compare the values.  (e.g. if (NEW.field != OLD.field) do
something;);

Can anyone help me with this?  Thank you in advance.

Tim Vadnais



Responses

pgsql-general by date

Next:From: Karsten HilbertDate: 2004-10-28 16:15:05
Subject: Re: Question Regarding Locks
Previous:From: Tom LaneDate: 2004-10-28 15:47:51
Subject: Re: what could cause inserts getting queued up and db locking??

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group