Thread

1. audit table

   Sim Zacks <sim@compulab.co.il> — 2009-02-12T09:29:41Z

   -----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-----
   
   

2. Re: audit table

   Craig Ringer <craig@postnewspapers.com.au> — 2009-02-13T03:13:05Z

   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
   
   

3. Re: audit table

   Robert Treat <xzilla@users.sourceforge.net> — 2009-02-17T02:36:40Z

   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