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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
