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, > -- tstamp timestamp 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
