Re: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column

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(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15533
> Logged by: Lulzim Bilali
> Email address: lulzimbilali(at)gmail(dot)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
>
>