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
