auditing question

From: Bohdan(dot)Linda <Bohdan(dot)Linda(at)seznam(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: auditing question
Date: 2007-07-11 17:05:12
Message-ID: 497.2-18100-1645197717-1184173512@seznam.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2007-07-11 17:06:22 Re: how does a temp table work?
Previous Message Joshua N Pritikin 2007-07-11 16:58:25 how does a temp table work?