> My problem is I defined the "before" and "after"
> fields in the audit table as TEXT and when I try to move NEW or OLD into
> these fields I get the error "NEW used in query that is not in a rule".  

You're trying to insert record data into a text field, that doesn't work.
OLD and NEW can be used as either record identifiers (as in RETURN OLD)
or column qualifiers (as in OLD.colname), but you can't intermingle them.

I don't think postgres (pl/pgsql) has row-to-variable and variable-to-row 
functions like serialize and unserialize, that's probably what you'd need. 
It would probably be necessary to write something like that in C, since 
at this point pl/perl cannot be used for trigger functions.  

I've not tried using pl/php yet, the announcement for it says it can be 
used for trigger functions.  

My first thought is that even if there was a serialize/unserialize 
capabiity you might be able to write something using it that creates 
the log entry but not anything that allows you to query the log for 
specific column or row entries.

It would probably require a MAJOR extension of SQL to add it to pg,
as there would need to be qualifiers that can be mapped to specific
tables and columns.  Even if we had that, storing values coming from 
multiple tables into a single audit table would present huge challenges.

I've found only two ways to implement audit logs:

1.  Have separate log tables that match the structure of
    the tables they are logging.

2.  Write a trigger function that converts columns to something you can
    store in a common log table.  (I've not found a way to do this without
    inserting one row for each column being logged, though.)
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to