pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug report for plpgsql
3+ messages / 3 participants
[nested] [flat]
* Bug report for plpgsql
@ 2024-11-28 23:29 Slivaev Dmitry <dmitry.slivaev@gmail.com>
2024-11-29 15:41 ` Re: Bug report for plpgsql David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Slivaev Dmitry @ 2024-11-28 23:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Postrgesql version
PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
Description:
When a RETURNS TABLE function in PL/pgSQL includes a column name that matches a column in a table being targeted for an ON CONFLICT clause, PostgreSQL throws an error stating that the column reference is ambiguous. This happens even though the context of ON CONFLICT unambiguously refers to table column names, not PL/pgSQL variables.
This behavior leads to unexpected errors in scenarios where the function's RETURNS TABLE includes columns with the same name as those in the target table. Explicit qualification of column names in ON CONFLICT (e.g., table.column) also fails to resolve the issue.
Error Message:
ERROR: column reference "column_name" is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
CONTEXT: PL/pgSQL function some_function() line X at SQL statement
Steps to Reproduce:
1. Create a table with a unique constraint:
CREATE TABLE example_table (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
column_name text NOT NULL,
value numeric NOT NULL,
UNIQUE (column_name)
);
1. Create a function that returns a table with a column name matching one in the table:
CREATE OR REPLACE FUNCTION example_function(p_column_name text, p_value numeric)
RETURNS TABLE(column_name text, value numeric)
LANGUAGE plpgsql
AS
$$
BEGININSERT INTO example_table (column_name, value)
VALUES (p_column_name, p_value)
ON CONFLICT (column_name)
DO UPDATE SET value = example_table.value + EXCLUDED.value
RETURNING column_name, value INTO column_name, value;
RETURN NEXT;
END;
$$;
1. Call the function:
SELECT * FROM example_function('test', 42);
Expected Behavior:
The function should execute successfully, as ON CONFLICT (column_name) unambiguously refers to the example_table.column_name.
Actual Behavior:
The function throws the following error:
ERROR: column reference "column_name" is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug report for plpgsql
2024-11-28 23:29 Bug report for plpgsql Slivaev Dmitry <dmitry.slivaev@gmail.com>
@ 2024-11-29 15:41 ` David G. Johnston <david.g.johnston@gmail.com>
2024-11-29 15:48 ` Re: Bug report for plpgsql Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2024-11-29 15:41 UTC (permalink / raw)
To: Slivaev Dmitry <dmitry.slivaev@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Thursday, November 28, 2024, Slivaev Dmitry <dmitry.slivaev@gmail.com>
wrote:
> Postrgesql version
>
> PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
>
>
> *Description:*
> When a RETURNS TABLE function in PL/pgSQL includes a column name that
> matches a column in a table being targeted for an ON CONFLICT clause,
> PostgreSQL throws an error stating that the column reference is ambiguous.
> This happens even though the context of ON CONFLICT unambiguously refers
> to table column names, not PL/pgSQL variables.
> This behavior leads to unexpected errors in scenarios where the function's RETURNS
> TABLE includes columns with the same name as those in the target table.
> Explicit qualification of column names in ON CONFLICT (e.g., table.column)
> also fails to resolve the issue.
>
Yes, this is a known limitation that has a very low priority for being
fixed. You can find more discussion on the mailing list archives.
https://www.postgresql.org/message-id/CAKFQuwb1AxxOBR-0BkUwZypjs8tqdFLSsTa46Xwxkkm+xdQxbg@mail.gmail...
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug report for plpgsql
2024-11-28 23:29 Bug report for plpgsql Slivaev Dmitry <dmitry.slivaev@gmail.com>
2024-11-29 15:41 ` Re: Bug report for plpgsql David G. Johnston <david.g.johnston@gmail.com>
@ 2024-11-29 15:48 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Pavel Stehule @ 2024-11-29 15:48 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Slivaev Dmitry <dmitry.slivaev@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi
pá 29. 11. 2024 v 16:41 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:
> On Thursday, November 28, 2024, Slivaev Dmitry <dmitry.slivaev@gmail.com>
> wrote:
>
>> Postrgesql version
>>
>> PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
>>
>>
>> *Description:*
>> When a RETURNS TABLE function in PL/pgSQL includes a column name that
>> matches a column in a table being targeted for an ON CONFLICT clause,
>> PostgreSQL throws an error stating that the column reference is ambiguous.
>> This happens even though the context of ON CONFLICT unambiguously refers
>> to table column names, not PL/pgSQL variables.
>> This behavior leads to unexpected errors in scenarios where the
>> function's RETURNS TABLE includes columns with the same name as those in
>> the target table. Explicit qualification of column names in ON CONFLICT
>> (e.g., table.column) also fails to resolve the issue.
>>
>
> Yes, this is a known limitation that has a very low priority for being
> fixed. You can find more discussion on the mailing list archives.
>
>
> https://www.postgresql.org/message-id/CAKFQuwb1AxxOBR-0BkUwZypjs8tqdFLSsTa46Xwxkkm+xdQxbg@mail.gmail...
>
Maybe this issue could be documented. Today is zero documented.
Regards
Pavel
> David J.
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-29 15:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-28 23:29 Bug report for plpgsql Slivaev Dmitry <dmitry.slivaev@gmail.com>
2024-11-29 15:41 ` David G. Johnston <david.g.johnston@gmail.com>
2024-11-29 15:48 ` Pavel Stehule <pavel.stehule@gmail.com>
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