Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
Dne po 16. 12. 2019 20:28 uživatel Tom Lane napsal: > Pavel Stehule writes: > > po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form < > > nore...@postgresql.org> napsal: > >> I'm wondering if it would be worthwhile to put a totally generic > auditing > >> function into the documentation e.g. > >> [ snip ] > > > Just few points to this code > > I agree this code could have better style, but maybe that is just more > evidence that a well-written example would be helpful? > +1 there is not too much examples for trigger parameters. > regards, tom lane >
Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
> On 16 Dec 2019, at 20:27, Tom Lane wrote: > > .. maybe that is just more > evidence that a well-written example would be helpful? I think thats the key takeaway here. +1 on the gist of the suggestion that started this thread. cheers ./daniel
Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
Pavel Stehule writes: > po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form < > nore...@postgresql.org> napsal: >> I'm wondering if it would be worthwhile to put a totally generic auditing >> function into the documentation e.g. >> [ snip ] > Just few points to this code I agree this code could have better style, but maybe that is just more evidence that a well-written example would be helpful? regards, tom lane
Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form < nore...@postgresql.org> napsal: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/12/plpgsql-trigger.html > Description: > > I'm wondering if it would be worthwhile to put a totally generic auditing > function into the documentation e.g. > > CREATE OR REPLACE FUNCTION zz_audit_() RETURNS TRIGGER AS $nothing$ > > -- This function is intended to be used by a delete/insert/update trigger > for any table. > -- It relies on the existence of a table named zz_audit_XXX (where XXX is > the table being audited) that contains the > -- same columns as the table XXX except that two additional columns must > exist prior to the columns from XXX > --operation character(1) NOT NULL, > --tstamptimestamp with time zone NOT NULL, > --... remaining columns per table XXX > > DECLARE audit_table_name NAME := CONCAT('zz_audit_', TG_TABLE_NAME); > BEGIN > > IF (TG_OP = 'DELETE') THEN > EXECUTE 'INSERT INTO ' || audit_table_name || ' SELECT ''D'', > now(), ' || ' $1.*' USING OLD; > ELSIF (TG_OP = 'UPDATE') THEN > EXECUTE 'INSERT INTO ' || audit_table_name || ' > SELECT ''U'', now(), ' || > ' $1.*' USING NEW; > ELSIF (TG_OP = 'INSERT') THEN > EXECUTE 'INSERT INTO ' || audit_table_name || ' > SELECT ''I'', now(), ' || > ' $1.*' USING NEW; > END IF; > > RETURN NULL; -- result is ignored since this is an AFTER trigger > END; > $nothing$ LANGUAGE plpgsql; > Just few points to this code 1. bad, useless brackets in IF .. ELSIF expressions - plpgsql is not C or Java 2. unescaped identifiers in dynamic SQL - EXECUTE 3. there is not reason for INSERT SELECT. Regards Pavel
Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/plpgsql-trigger.html Description: I'm wondering if it would be worthwhile to put a totally generic auditing function into the documentation e.g. CREATE OR REPLACE FUNCTION zz_audit_() RETURNS TRIGGER AS $nothing$ -- This function is intended to be used by a delete/insert/update trigger for any table. -- It relies on the existence of a table named zz_audit_XXX (where XXX is the table being audited) that contains the -- same columns as the table XXX except that two additional columns must exist prior to the columns from XXX --operation character(1) NOT NULL, --tstamptimestamp with time zone NOT NULL, --... remaining columns per table XXX DECLARE audit_table_name NAME := CONCAT('zz_audit_', TG_TABLE_NAME); BEGIN IF (TG_OP = 'DELETE') THEN EXECUTE 'INSERT INTO ' || audit_table_name || ' SELECT ''D'', now(), ' || ' $1.*' USING OLD; ELSIF (TG_OP = 'UPDATE') THEN EXECUTE 'INSERT INTO ' || audit_table_name || ' SELECT ''U'', now(), ' || ' $1.*' USING NEW; ELSIF (TG_OP = 'INSERT') THEN EXECUTE 'INSERT INTO ' || audit_table_name || ' SELECT ''I'', now(), ' || ' $1.*' USING NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $nothing$ LANGUAGE plpgsql;