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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
