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@sss.pgh.pa.us> wrote: Andrew Sullivan <ajs@crankycanuck.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