Re: Fwd: Start up question about triggers

This is why I was searching for good meta data.

Here is a thought. If your trigger has the OLD and NEW, is there a way to
get a list of fields from OLD and NEW? If TG_RELNAME is the name of the
table, could you just ask PostgreSQL what the columns are in that table,
iterate through those columns, get the values for each of these columns out
of OLD and NEW and save the old/new values?

What I really cannot find is a way to _dynamically_ in the trigger ask what
COLUMNS are in OLD and NEW. If we had:

- table affected (TG_RELNAME?)
- columns that are in the table
- old values for each of these columns
- new values for each of these columns

Then you could store this information into two tables:

modify_table
modify_table_id
modify_dt
table_name

modify_value
modify_value_id
modify_table_id
old_value
new_value

I wish I had more experience with stored procedures - I know what I would
try to do, just not if it is possible or how to implement it.

Tom makes a very good point that having the actual query is not going to
help in a general sense. If someone does an insert or update which fires a
trigger that does further updates and inserts or even changes values on the
fly, the inserts and updates you record will NOT reveal exactly what is
going on. Keeping the values from OLD and NEW at the very end would be much
more useful.

-Aaron Bono

On 6/23/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> > On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote:
> >> Then there exist a TG_QUERY parameter that we could use to get the
> actual
> >> query ran by a user, so if I ran the imaginary query
>
> > Which "actual query"? By the time the trigger fires, the query might
> > already have been rewritten, I think. No? I _think_ that even
> > BEFORE triggers happen after the rewriter stage is called, but
> > someone who has more clue will be able to correct me if I'm wrong.
>
> Even if you could get hold of the user query text, it'd be a serious
> mistake to imagine that it tells you everything you need to know about
> the update. Aside from rule rewrites, previous BEFORE triggers could
> have changed fields that are mentioned nowhere in the query. The only
> safe way to determine what's going on is to compare the OLD and NEW
> row values.
>
> regards, tom lane
>
>