PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15533
PG Version11.1
OSUbuntu 18.04
Opened2018-11-30 20:17:20+00
Reported byLulzim Bilali
StatusOpen

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15533
Logged by:          Lulzim Bilali
Email address:      (redacted)
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

Messages

DateAuthorSubject
2018-11-30 20:17:20+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column
2018-11-30 23:05:40+00Pantelis TheodosiouRe: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column
2018-12-01 10:49:05+00Lulzim BilaliRe: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column
2018-12-01 11:17:28+00Pavel StehuleRe: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column
2018-12-01 11:31:57+00Andrew GierthRe: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column
2018-12-01 11:57:27+00Pavel StehuleRe: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column
2018-12-01 12:21:54+00Andrew GierthRe: BUG #15533: error on upsert when used in a fuction and a function parameter has the same name as the column