auditing question

Поиск
Список
Период
Сортировка
От Bohdan.Linda
Тема auditing question
Дата
Msg-id 497.2-18100-1645197717-1184173512@seznam.cz
обсуждение исходный текст
Ответы Re:[GENERAL] auditing question - PARTIALY SOLVED  (Bohdan.Linda <Bohdan.Linda@seznam.cz>)
Список pgsql-general
Hello,

I am restricted to 8.0.7 version of postgresql and I am facing two problems
when trying to build generic auditing function. I went through documentation

http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html

example:
Example 35-3. A PL/pgSQL Trigger Procedure For Auditing

had rewritten it into format:

audit.<table> is copy of <table> without constraints and also inherits some
data from generic auditing table (as name, when, ...)

=====================

CREATE OR REPLACE FUNCTION audit_table() RETURNS trigger AS $$
DECLARE
    _name TEXT;
BEGIN
    -- Get current user
    SELECT INTO _name CURRENT_USER;

    IF TG_OP = 'DELETE' THEN
        EXECUTE 'INSERT INTO audit.' || TG_RELNAME ||
                ' SELECT _name, now(), OLD.*;';
        RETURN OLD;
    ELSIF TG_OP = 'INSERT' THEN
        EXECUTE 'INSERT INTO audit.' || TG_RELNAME ||
                ' SELECT _name, now(), NEW.*;';
        RETURN NEW;
...

=====================

and binding the procedure to trigger AFTER INSERT,DELETE, UPDATE, gives me problem:

ERROR:  NEW used in query that is not in a rule
CONTEXT:  SQL statement "INSERT INTO audit.communities SELECT _name, now(), row(NEW);"

Thus do I do something wrong or example is not compatible with my version? Even
tried to remove EXECUTE and limited it to one table only - no help. I
like to use this approach instead of rules, while I can setup SECURITY DEFINER
on procedure and therefore do not need to solve permissions on audit tables.

Going further rules and triggers give me NEW and OLD records at my disposal. Can
these records be comparable "at-once"? Imagine table, which has significant
number of attributes so you do not want to list them explicitly in condition.
Especially when you have more such tables.

Even any generic function which will take two records as parameter, compare them
and return BOOLEAN is enough

Thank you for help,
Bohdan


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

Предыдущее
От: Andrew Sullivan
Дата:
Сообщение: Re: how does a temp table work?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: troubble with contrib compile