Search Postgresql Archives

Re: audit table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux