Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing

po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form <
noreply@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

Pavel Stehule <pavel.stehule@gmail.com> writes:

po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form <
noreply@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

On 16 Dec 2019, at 20:27, Tom Lane <tgl@sss.pgh.pa.us> 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

Dne po 16. 12. 2019 20:28 uživatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Pavel Stehule <pavel.stehule@gmail.com> writes:

po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form <
noreply@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.