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

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@gmail.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@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