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

Hi

so 1. 12. 2018 v 12:02 odesílatel Lulzim Bilali <lulzimbilali(at)gmail(dot)com>
napsal:

> In my opinion this is a bug and what you are suggesting is a work around
> for this particular case. what if we have a function where somewhere else
> we need the variable first?
>

every time any possible conflict between SQL and plpgsql identifier is
terrible bad issue and it is a source of very hidden errors.

You can prefer SQL before plpgsql identifiers (like Oracle), or plpgsql
before SQL (like old Postgres) or raise error on conflict (current
Postgres).

I am strongly sure, so current default is best and any change of this
behave (it is simply - just use #option) is strongly wrong.

You can use a) alias b) prefixes

so very safe is using

DECLARE _id int;
BEGIN
..
WHERE id = _id

or

<<blocklabel>>
DECLARE id int;
BEGIN
SELECT * FROM tab WHERE tab.id = blocklabel.id

or

CREATE OR REPLACE FUNCTION fname(id int)
...

BEGIN
SELECT * FROM tab WHERE tab.id = fname.id

So current behave is different than Oracle or old Postgres, but it is SAFE!
It doesn't block any necessary functionality, just it show any possible
issue.

Regards

Pavel

>
> My point is that ON CONFLICT (<variable_name>) DO UPDATE is not a valid
> construct an as such PostgreSQL should know that and try to use only the
> column.
>
> And if both can be used than it should be possible to use the full name
> like log_tst.id or tst.id.
>
> Lulzim
>
> On Sat, Dec 1, 2018 at 12:05 AM Pantelis Theodosiou <ypercube(at)gmail(dot)com>
> wrote:
>
>> 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
>>>
>>>