audit tables adding columns

Поиск
Список
Период
Сортировка
От Jebus
Тема audit tables adding columns
Дата
Msg-id c76867810602271412i60176ec9l4e09ccf0ba9e366b@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Fuhr
Дата:
Сообщение: Re: Breaking Path/Polygon Data into Pieces
Следующее
От: Christopher Weimann
Дата:
Сообщение: Re: Wish: remove ancient constructs from Postgres