Re: BUG #15533: error on upsert when used in a fuction and a functionparameter has the same name as the column - Mailing list pgsql-bugs

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