Re: audit table

Sim Zacks wrote:

> I want a trigger on every table that inserts the old row into an audit
> table (for updates and deletes). If the audit table was per table, then
> I could easily have a field of type that table and insert old into it.

> Is there any way that I could accomplish this functionality with any
> other type, so I could input any record into it?

You want a single audit table that looks like this:

CREATE TABLE audit (
   id SERIAL PRIMARY KEY,
   table_changed regclass,
   changed_by VARCHAR,
   changed_when TIMESTAMP WITH TIME ZONE,
   oldrow ANY_ROW_TYPE
);

ie you want a field that can dynamically contain anything?

AFAIK that's not possible unless you want to store a textual
representation of the row. I'm not sure of an easy way to do it even
then, and of course you can't read it out again as a real row.

What you might want to look at doing is using table inheritance. Your
master audit table looks like this:

CREATE TABLE audit (
   id SERIAL PRIMARY KEY,
   table_changed regclass,
   changed_by VARCHAR,
   changed_when TIMESTAMP WITH TIME ZONE,
);

and then you have child audit tables for each audited table, each of
which looks like this:

CREATE TABLE audit_tablename (
    old_row tablename;
) INHERITS audit;

--
Craig Ringer