public inbox for [email protected]  

help / color / mirror / Atom feed

From: Pavel Stehule <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
Date: Mon, 16 Dec 2019 12:25:34 +0100
Message-ID: <CAFj8pRCnPmD8j2v+Sh5zsqvR2ef+tNqSLecJXeZOr8hCgrCb9g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form <
[email protected]> 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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
  In-Reply-To: <CAFj8pRCnPmD8j2v+Sh5zsqvR2ef+tNqSLecJXeZOr8hCgrCb9g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox