[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-bugs
Subject: Re: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as
From: Pantelis Theodosiou <ypercube () gmail ! com>
Date: 2018-11-30 23:05:40
Message-ID: CAE3TBxzoE9H8jY8L0fJ=BV2e7sHvSFEUaExYFqrVixBZG6x6qQ () mail ! gmail ! com
[Download RAW message or body]
I don't think this is a bug.
You can decide how conflicts are resolved with the pgplsql parameter
variable_conflict:
CREATE OR REPLACE FUNCTION log_tst(id int, info text)
RETURNS void AS
$$
#variable_conflict use_column
BEGIN
INSERT INTO tst (id, info)
VALUES (log_tst.id, log_tst.info)
--ON CONFLICT DO NOTHING
ON CONFLICT (id) DO UPDATE
SET info = log_tst.info ;
END $$
LANGUAGE plpgsql;
See the documentation:
https://www.postgresql.org/docs/current/plpgsql-implementation.html
Pantelis Theodosio
On Fri, Nov 30, 2018 at 8:18 PM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15533
> Logged by: Lulzim Bilali
> Email address: lulzimbilali@gmail.com
> PostgreSQL version: 11.1
> Operating system: Ubuntu 18.04
> Description:
>
> Can't use `ON CONFLICT DO UPDATE` in a function which has a parameter with
> the same name as the column where the unique key is.
>
> Here is the error I get.
>
> Query execution failed
>
> Reason:
> SQL Error [42702]: ERROR: column reference "id" is ambiguous
> Detail: It could refer to either a PL/pgSQL variable or a table
> column.
> Where: PL/pgSQL function log_tst(integer,text) line 4 at SQL
> statement
>
> the test code I'm using:
>
>
> --DROP TABLE IF EXISTS tst;
> CREATE TABLE tst (
> id int UNIQUE,
> info text
> );
>
> --DROP FUNCTION IF EXISTS log_tst;
> CREATE OR REPLACE FUNCTION log_tst(id int, info text) RETURNS void AS
> $$
> BEGIN
>
> INSERT INTO tst (id, info)
> VALUES (log_tst.id, log_tst.info)
> --ON CONFLICT DO NOTHING
> ON CONFLICT (id) DO UPDATE
> SET info = log_tst.info
> ;
> END $$
> LANGUAGE plpgsql;
>
> SELECT log_tst(1, 'changed');
>
> I would expect it to work since we can't use a parameter to check the
> uniqueness even if we want (or can we!?), so PostgreSQL should know to use
> the column instead.
>
> Lulzim
>
>
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr"><div dir="ltr"><div>I don't think this is a \
bug.</div><div><br></div><div>You can decide how conflicts are resolved with the \
pgplsql parameter <span \
style="font-family:monospace,monospace">variable_conflict:</span></div><div><br></div><div><span \
style="font-family:monospace,monospace"> CREATE OR REPLACE FUNCTION log_tst(id \
int, info text)</span></div><div><span style="font-family:monospace,monospace"> \
RETURNS void AS<br> $$</span></div><div><span \
style="font-family:monospace,monospace"> #variable_conflict use_column <br> \
BEGIN<br><br> INSERT INTO tst (id, info)<br> VALUES (<a \
href="http://log_tst.id">log_tst.id</a>, <a \
href="http://log_tst.info">log_tst.info</a>)<br> --ON CONFLICT DO \
NOTHING<br> ON CONFLICT (id) DO UPDATE<br> SET info = <a \
href="http://log_tst.info">log_tst.info</a></span> <span \
style="font-family:monospace,monospace">;<br> END $$<br> LANGUAGE \
plpgsql;<br></span><br></div><div>See the documentation: <a \
href="https://www.postgresql.org/docs/current/plpgsql-implementation.html">https://www \
.postgresql.org/docs/current/plpgsql-implementation.html</a></div><div><br></div><div>Pantelis \
Theodosio<br></div></div></div></div><br><div class="gmail_quote"><div dir="ltr">On \
Fri, Nov 30, 2018 at 8:18 PM PG Bug reporting form <<a \
href="mailto:noreply@postgresql.org">noreply@postgresql.org</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex">The following bug has been logged \
on the website:<br> <br>
Bug reference: 15533<br>
Logged by: Lulzim Bilali<br>
Email address: <a href="mailto:lulzimbilali@gmail.com" \
target="_blank">lulzimbilali@gmail.com</a><br> PostgreSQL version: 11.1<br>
Operating system: Ubuntu 18.04<br>
Description: <br>
<br>
Can't use `ON CONFLICT DO UPDATE` in a function which has a parameter with<br>
the same name as the column where the unique key is.<br>
<br>
Here is the error I get.<br>
<br>
Query execution failed<br>
<br>
Reason:<br>
SQL Error [42702]: ERROR: column reference "id" is ambiguous<br>
Detail: It could refer to either a PL/pgSQL variable or a table<br>
column.<br>
Where: PL/pgSQL function log_tst(integer,text) line 4 at SQL<br>
statement<br>
<br>
the test code I'm using:<br>
<br>
<br>
--DROP TABLE IF EXISTS tst;<br>
CREATE TABLE tst (<br>
id int UNIQUE,<br>
info text<br>
);<br>
<br>
--DROP FUNCTION IF EXISTS log_tst;<br>
CREATE OR REPLACE FUNCTION log_tst(id int, info text) RETURNS void AS<br>
$$<br>
BEGIN<br>
<br>
INSERT INTO tst (id, info)<br>
VALUES (<a href="http://log_tst.id" rel="noreferrer" \
target="_blank">log_tst.id</a>, <a href="http://log_tst.info" rel="noreferrer" \
target="_blank">log_tst.info</a>)<br>
--ON CONFLICT DO NOTHING<br>
ON CONFLICT (id) DO UPDATE<br>
SET info = <a href="http://log_tst.info" rel="noreferrer" \
target="_blank">log_tst.info</a><br> ;<br>
END $$<br>
LANGUAGE plpgsql;<br>
<br>
SELECT log_tst(1, 'changed');<br>
<br>
I would expect it to work since we can't use a parameter to check the<br>
uniqueness even if we want (or can we!?), so PostgreSQL should know to use<br>
the column instead.<br>
<br>
Lulzim<br>
<br>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic