pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedTypo in PL/pgSQL trigger Example 43.4?
6+ messages / 4 participants
[nested] [flat]
* Typo in PL/pgSQL trigger Example 43.4?
@ 2023-10-07 18:10 Kirk Parker <khp@equatoria.us>
0 siblings, 1 reply; 6+ messages in thread
From: Kirk Parker @ 2023-10-07 18:10 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
The PL/pgSQL page on triggers (
https://www.postgresql.org/docs/16/plpgsql-trigger.html ) contains the
following example (I'm excerpting only the essential parts here):
Example 43.4. A PL/pgSQL Trigger Function for Auditing
...
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL, -- <= COLUMN IN QUESTION
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS
$emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed
on emp,
-- making use of the special variable TG_OP to work out the
operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <=
ARGUMENT IN QUESTION
-- similar code with same issue follows for the other TG_OPs...
The emp_audit table has a column named 'userid', which in actual usage
(next-to-last line quoted) is populated by 'user' which seems undefined in
the context. Was that intended to be 'current_user', or am I missing
something?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo in PL/pgSQL trigger Example 43.4?
@ 2023-10-07 18:15 David G. Johnston <david.g.johnston@gmail.com>
parent: Kirk Parker <khp@equatoria.us>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2023-10-07 18:15 UTC (permalink / raw)
To: Kirk Parker <khp@equatoria.us>; +Cc: pgsql-docs@lists.postgresql.org
On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker <khp@equatoria.us> wrote:
>
> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <=
> ARGUMENT IN QUESTION
> The emp_audit table has a column named 'userid', which in actual usage
> (next-to-last line quoted) is populated by 'user' which seems undefined in
> the context. Was that intended to be 'current_user', or am I missing
> something?
>
user is a valid pseudo-function:
https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo in PL/pgSQL trigger Example 43.4?
@ 2023-10-07 20:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2023-10-07 20:22 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Kirk Parker <khp@equatoria.us>; pgsql-docs@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker <khp@equatoria.us> wrote:
>> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN QUESTION
>> The emp_audit table has a column named 'userid', which in actual usage
>> (next-to-last line quoted) is populated by 'user' which seems undefined in
>> the context. Was that intended to be 'current_user', or am I missing
>> something?
> user is a valid pseudo-function:
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION
Yeah, either way has the same result. However, I wonder if we should
change this example to use current_user for clarity. It does look
more like it's intended to be a variable or column reference than
a built-in function.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo in PL/pgSQL trigger Example 43.4?
@ 2023-10-09 04:36 Kirk Parker <khp@equatoria.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Kirk Parker @ 2023-10-09 04:36 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
On Sat, Oct 7, 2023 at 1:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker <khp@equatoria.us> wrote:
> >> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN
> QUESTION
> >> The emp_audit table has a column named 'userid', which in actual usage
> >> (next-to-last line quoted) is populated by 'user' which seems undefined
> in
> >> the context. Was that intended to be 'current_user', or am I missing
> >> something?
>
> > user is a valid pseudo-function:
> >
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION
>
> Yeah, either way has the same result. However, I wonder if we should
> change this example to use current_user for clarity. It does look
> more like it's intended to be a variable or column reference than
> a built-in function.
>
>
Since the previous example on the page uses 'current_user' (which I
suppose is why I didn't look further to see if 'user' was also a function),
perhaps that would be a good idea.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo in PL/pgSQL trigger Example 43.4?
@ 2023-10-09 08:53 Daniel Gustafsson <daniel@yesql.se>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Daniel Gustafsson @ 2023-10-09 08:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Kirk Parker <khp@equatoria.us>; pgsql-docs@lists.postgresql.org
> On 7 Oct 2023, at 22:22, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker <khp@equatoria.us> wrote:
>>> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <= ARGUMENT IN QUESTION
>>> The emp_audit table has a column named 'userid', which in actual usage
>>> (next-to-last line quoted) is populated by 'user' which seems undefined in
>>> the context. Was that intended to be 'current_user', or am I missing
>>> something?
>
>> user is a valid pseudo-function:
>> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION
>
> Yeah, either way has the same result. However, I wonder if we should
> change this example to use current_user for clarity. It does look
> more like it's intended to be a variable or column reference than
> a built-in function.
Agreed, and "user" is a hard search term to use for discovering what it is. +1
for changing to current_user.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Typo in PL/pgSQL trigger Example 43.4?
@ 2023-10-09 13:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2023-10-09 13:45 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Kirk Parker <khp@equatoria.us>; pgsql-docs@lists.postgresql.org
Daniel Gustafsson <daniel@yesql.se> writes:
>> On 7 Oct 2023, at 22:22, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, either way has the same result. However, I wonder if we should
>> change this example to use current_user for clarity. It does look
>> more like it's intended to be a variable or column reference than
>> a built-in function.
> Agreed, and "user" is a hard search term to use for discovering what it is. +1
> for changing to current_user.
OK, I'll take care of this later today.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-10-09 13:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-07 18:10 Typo in PL/pgSQL trigger Example 43.4? Kirk Parker <khp@equatoria.us>
2023-10-07 18:15 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-07 20:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-09 04:36 ` Kirk Parker <khp@equatoria.us>
2023-10-09 08:53 ` Daniel Gustafsson <daniel@yesql.se>
2023-10-09 13:45 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox