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