Обсуждение: auditing question

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

Hello,

with help of Bricklen, I have found that the problem is the scope of call EXECUTE. When this is called it seems to be
"launched"outside of the trigger's scope and OLD/NEW is not defined. (Manual suggests it) 

The remaining question is how to compare OLD.* and NEW.* in generic way for 8.0.x version

Regards,
Bohdan

>
>     IF TG_OP = 'DELETE' THEN
>         EXECUTE 'INSERT INTO audit.' || TG_RELNAME ||
>                 ' SELECT _name, now(), OLD.*;';
>         RETURN OLD;
> =====================
>
> 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);"