On Thursday 12 February 2009 22:13:05 Craig Ringer wrote: > 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; > http://pgfoundry.org/projects/tablelog/ -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
