> 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