Thread: audit table
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 The documentation says you cannot use a pseudo type for a table column, so I was wondering if there is any way to handle what I would like to do. 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? When I read it out, I would obviously cast it out to the correct type. Sim -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmT7AQACgkQjDX6szCBa+oJlwCfWslrC9X2Y7XQRanuF1vQJ7uW y/oAoOdL0Nt8O+GdmP4is3CalLQto7+s =4mxe -----END PGP SIGNATURE-----
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
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