audit tables adding columns

From: Jebus <lordjebus(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: audit tables adding columns
Date: 2006-02-27 22:12:44
Message-ID: c76867810602271412i60176ec9l4e09ccf0ba9e366b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am using triggers and table inheritance for my audit tables. Here is
the function I am using its straight copy from the docs.

CREATE OR REPLACE FUNCTION process_reward_audit()
RETURNS "trigger" AS
$BODY$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO reward_audit SELECT NEW.*, NOW(), user, 'I';
RETURN NEW;
ELSEIF (TG_OP = 'UPDATE') THEN
INSERT INTO reward_audit SELECT NEW.*,NOW(), user, 'U';
RETURN NEW;
ELSEIF (TG_OP = 'DELETE') THEN
INSERT INTO reward_audit SELECT OLD.*,NOW(), user, 'D';
RETURN OLD;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

The problem I am having is if I have to add a column to the table I am
auditing, the new column gets added to the end of the parent table AND
to the child audit table. This causes my function to die because NEW.*
now has the new column that is trying to be inserted into date column
of the audit table.

Since postgres doesn't allow you to reorder column I can't move the
new column in the audit table so I have to drop my audit table and
recreate it. I am basically at the initial stages of development on a
web app so I don't care about the data in the audit table, but I will
when it goes live. The ideal solution would be if I could change the
above function to magically handle new columns I can't think of anyway
this is going to happen. I am thinking I'll end up writing a script to
partial automate the dump of and recreation of the audit table, but I
thought I'd put this out there and see if wonderfully people of the
postgres community had some magically solution.

joe

Browse pgsql-general by date

  From Date Subject
Next Message Christopher Weimann 2006-02-27 22:56:32 Re: Wish: remove ancient constructs from Postgres
Previous Message Michael Fuhr 2006-02-27 22:10:40 Re: Breaking Path/Polygon Data into Pieces