Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing - Mailing list pgsql-docs

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;