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

From: Lulzim Bilali <lulzimbilali(at)gmail(dot)com>
To: ypercube(at)gmail(dot)com
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column
Date: 2018-12-01 10:49:05
Message-ID: CALeMZxkvSVtF_vurZKksh97kwT+ugJJh2XPiSTPdvO6=0Dr-xw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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?

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
>>
>>

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavel Stehule 2018-12-01 11:17:28 Re: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column
Previous Message Peter Geoghegan 2018-12-01 06:14:25 Re: BUG #15528: on v11.0 version still get error "ERROR: catalog is missing 1 attribute(s) for relid 6855092"