Date: Fri, 29 Nov 2024 02:29:39 +0300
From: Slivaev Dmitry <dmitry.slivaev@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Message-ID: <818b6dcf-f202-428e-ac7b-3d197810458d@Spark>
In-Reply-To: <84b5a80c-e552-43a9-bd10-85ece52747fc@Spark>
References: <84b5a80c-e552-43a9-bd10-85ece52747fc@Spark>
Subject: Bug report for plpgsql
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="6748fce8_7fb7e0aa_257"
Archived-At: 
 <https://www.postgresql.org/message-id/818b6dcf-f202-428e-ac7b-3d197810458d%40Spark>
Precedence: bulk

--6748fce8_7fb7e0aa_257
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Postrgesql version
PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on x86=5F64-pc-linux-gnu, compi=
led by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Description:
When a=C2=A0RETURNS TABLE=C2=A0function in PL/pgSQL includes a column nam=
e that matches a column in a table being targeted for an=C2=A0ON CON=46LI=
CT=C2=A0clause, PostgreSQL throws an error stating that the column refere=
nce is ambiguous. This happens even though the context of=C2=A0ON CON=46L=
ICT=C2=A0unambiguously refers to table column names, not PL/pgSQL variabl=
es.
This behavior leads to unexpected errors in scenarios where the function'=
s=C2=A0RETURNS TABLE=C2=A0includes columns with the same name as those in=
 the target table. Explicit qualification of column names in=C2=A0ON CON=46=
LICT=C2=A0(e.g.,=C2=A0table.column) also fails to resolve the issue.

Error Message:
ERROR: column reference =22column=5Fname=22 is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
CONTEXT: PL/pgSQL function some=5Ffunction() line X at SQL statement
Steps to Reproduce:

1. Create a table with a unique constraint:

CREATE TABLE example=5Ftable (
   id uuid PRIMARY KEY DE=46AULT gen=5Frandom=5Fuuid(),
   column=5Fname text NOT NULL,
   value numeric NOT NULL,
   UNIQUE (column=5Fname)
);

1. Create a function that returns a table with a column name matching one=
 in the table:

CREATE OR REPLACE =46UNCTION example=5Ffunction(p=5Fcolumn=5Fname text, p=
=5Fvalue numeric)
   RETURNS TABLE(column=5Fname text, value numeric)
   LANGUAGE plpgsql
AS
=24=24
BEGININSERT INTO example=5Ftable (column=5Fname, value)
   VALUES (p=5Fcolumn=5Fname, p=5Fvalue)
   ON CON=46LICT (column=5Fname)
   DO UPDATE SET value =3D example=5Ftable.value + EXCLUDED.value
   RETURNING column=5Fname, value INTO column=5Fname, value;

   RETURN NEXT;
END;
=24=24;

1. Call the function:

SELECT * =46ROM example=5Ffunction('test', 42);
Expected Behavior:
The function should execute successfully, as=C2=A0ON CON=46LICT (column=5F=
name)=C2=A0unambiguously refers to the=C2=A0example=5Ftable.column=5Fname=
.
Actual Behavior:
The function throws the following error:
ERROR: column reference =22column=5Fname=22 is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.


--6748fce8_7fb7e0aa_257
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Postrgesql version<br /></div>
<pre>PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on x86=5F64-pc-linux-gnu, =
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit</pre>
<div dir=3D=22auto=22><span style=3D=22font-size: 14px=22><br /></span><s=
trong style=3D=22font-size: 14px=22>Description:</strong><span style=3D=22=
font-size: 14px=22><br /></span>When a&=23160;<code>RETURNS TABLE</code>&=
=23160;function in PL/pgSQL includes a column name that matches a column =
in a table being targeted for an&=23160;<code>ON CON=46LICT</code>&=23160=
;clause, PostgreSQL throws an error stating that the column reference is =
ambiguous. This happens even though the context of&=23160;<code>ON CON=46=
LICT</code>&=23160;unambiguously refers to table column names, not PL/pgS=
QL variables.<br />
This behavior leads to unexpected errors in scenarios where the function'=
s&=23160;<code>RETURNS TABLE</code>&=23160;includes columns with the same=
 name as those in the target table. Explicit qualification of column name=
s in&=23160;<code>ON CON=46LICT</code>&=23160;(e.g.,&=23160;<code>table.c=
olumn</code>) also fails to resolve the issue.<br />
<br />
<strong style=3D=22font-size: 14px=22>Error Message:</strong></div>
<pre>ERROR: column reference =22column=5Fname=22 is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
CONTEXT: PL/pgSQL function some=5Ffunction() line X at SQL statement</pre=
>
<div dir=3D=22auto=22><strong style=3D=22font-size: 14px=22>Steps to Repr=
oduce:</strong></div>
<ol type=3D=221=22>
<li>Create a table with a unique constraint:</li>
</ol>
<pre>CREATE TABLE example=5Ftable (
    id uuid PRIMARY KEY DE=46AULT gen=5Frandom=5Fuuid(),
    column=5Fname text NOT NULL,
    value numeric NOT NULL,
    UNIQUE (column=5Fname)
);</pre>
<ol type=3D=221=22>
<li>Create a function that returns a table with a column name matching on=
e in the table:</li>
</ol>
<pre>CREATE OR REPLACE =46UNCTION example=5Ffunction(p=5Fcolumn=5Fname te=
xt, p=5Fvalue numeric)
    RETURNS TABLE(column=5Fname text, value numeric)
    LANGUAGE plpgsql
AS
=24=24
BEGININSERT INTO example=5Ftable (column=5Fname, value)
    VALUES (p=5Fcolumn=5Fname, p=5Fvalue)
    ON CON=46LICT (column=5Fname)
    DO UPDATE SET value =3D example=5Ftable.value + EXCLUDED.value
    RETURNING column=5Fname, value INTO column=5Fname, value;

    RETURN NEXT;
END;
=24=24;</pre>
<ol type=3D=221=22>
<li>Call the function:</li>
</ol>
<pre style=3D=22font-size: 14px=22>SELECT * =46ROM example=5Ffunction('te=
st', 42);</pre>
<div dir=3D=22auto=22><strong style=3D=22font-size: 14px=22>Expected Beha=
vior:</strong><span style=3D=22font-size: 14px=22><br /></span>The functi=
on should execute successfully, as&=23160;<code>ON CON=46LICT (column=5Fn=
ame)</code>&=23160;unambiguously refers to the&=23160;<code>example=5Ftab=
le.column=5Fname</code>.<br />
<strong style=3D=22font-size: 14px=22>Actual Behavior:</strong><span styl=
e=3D=22font-size: 14px=22><br /></span>The function throws the following =
error:</div>
<pre>ERROR: column reference =22column=5Fname=22 is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.</=
pre>
<div dir=3D=22auto=22><br /></div>
</div>
</body>
</html>

--6748fce8_7fb7e0aa_257--