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