public inbox for [email protected]
help / color / mirror / Atom feedRe: Example 42.4. A PL/pgSQL Trigger Function for Auditing
5+ messages / 4 participants
[nested] [flat]
* Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
@ 2019-12-15 22:35 PG Doc comments form <[email protected]>
2019-12-16 11:25 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Pavel Stehule <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: PG Doc comments form @ 2019-12-15 22:35 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
2019-12-15 22:35 Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing PG Doc comments form <[email protected]>
@ 2019-12-16 11:25 ` Pavel Stehule <[email protected]>
2019-12-16 19:27 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2019-12-16 11:25 UTC (permalink / raw)
To: [email protected]; [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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
2019-12-15 22:35 Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing PG Doc comments form <[email protected]>
2019-12-16 11:25 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Pavel Stehule <[email protected]>
@ 2019-12-16 19:27 ` Tom Lane <[email protected]>
2019-12-16 19:35 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Daniel Gustafsson <[email protected]>
2019-12-16 19:42 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Pavel Stehule <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2019-12-16 19:27 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: [email protected]; [email protected]
Pavel Stehule <[email protected]> writes:
> po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form <
> [email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
2019-12-15 22:35 Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing PG Doc comments form <[email protected]>
2019-12-16 11:25 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Pavel Stehule <[email protected]>
2019-12-16 19:27 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Tom Lane <[email protected]>
@ 2019-12-16 19:35 ` Daniel Gustafsson <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Daniel Gustafsson @ 2019-12-16 19:35 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pavel Stehule <[email protected]>; [email protected]; [email protected]
> On 16 Dec 2019, at 20:27, Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing
2019-12-15 22:35 Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing PG Doc comments form <[email protected]>
2019-12-16 11:25 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Pavel Stehule <[email protected]>
2019-12-16 19:27 ` Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing Tom Lane <[email protected]>
@ 2019-12-16 19:42 ` Pavel Stehule <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2019-12-16 19:42 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]; [email protected]
Dne po 16. 12. 2019 20:28 uživatel Tom Lane <[email protected]> napsal:
> Pavel Stehule <[email protected]> writes:
> > po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form <
> > [email protected]> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-12-16 19:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-15 22:35 Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing PG Doc comments form <[email protected]>
2019-12-16 11:25 ` Pavel Stehule <[email protected]>
2019-12-16 19:27 ` Tom Lane <[email protected]>
2019-12-16 19:35 ` Daniel Gustafsson <[email protected]>
2019-12-16 19:42 ` Pavel Stehule <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox