sqlpostgresqlplpgsqlupsertname-conflict

How to disambiguate a plpgsql variable name in a ON CONFLICT clause?


Given this table:

create table test (
    name text primary key
);

I need to write a plpgsql function with a variable name that collides with the primary key name, which I must use in a on conflict clause:

create or replace function func(
    name text                               -- this variable name...
) returns void language plpgsql as 
$$
begin 
    insert into test (name) values (name) 
    on conflict (name) do update            -- ...conflicts with this line
    set name = func.name; 
end; 
$$;

This compiles, but then throws an ambiguous column reference:

select * from func('one');
ERROR:  column reference "name" is ambiguous
LINE 2:     on conflict (name) do update 
                        ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  insert into test (name) values (name) 
    on conflict (name) do update 
    set name = func.name
CONTEXT:  PL/pgSQL function func(text) line 3 at SQL statement

I tried specifying the full column name as on conflict (test.name) which does not compile, or ((test.name)) which compiles:

create or replace function func(
    name text
) returns void language plpgsql as 
$$
begin 
    insert into test (name) values (name) 
    on conflict ((test.name)) do            -- this fails too
    update set name = func.name; 
end; 
$$;

But it fails as well:

select * from func('two');
ERROR:  invalid reference to FROM-clause entry for table "test"
LINE 2:     on conflict ((test.name)) do 
                          ^
HINT:  There is an entry for table "test", but it cannot be referenced from this part of the query.
QUERY:  insert into test (name) values (name) 
    on conflict ((test.name)) do 
    update set name = func.name
CONTEXT:  PL/pgSQL function func(text) line 3 at SQL statement

Is there a solution?


Edit: I found a workaround:

on conflict on constraint test_pkey do update

where test_pkey is the table name plus _pkey. I don't know how reliable this is though. I'd still like to specify the column name instead.


Solution

  • to start with, name is a bad name for both variable and attribute. When you have both, code won't look good. with that in mind, you can "prefix" variable with labeled block (in example below <<fn>>), and set variable_conflict to give preference to column name, see code below:

    t=# create or replace function func(
        name text
    ) returns void language plpgsql as
    $$
    #variable_conflict use_column
    <<fn>>
    declare name text :='blah';
    begin
        insert into test (name) values (name)
        on conflict (name) do            -- this no longer fails
        update set name = fn.name;
    end;
    $$;
    t=# insert into test select 'b';
    INSERT 0 1
    Time: 8.076 ms
    t=# select func('b');
     func
    ------
    
    (1 row)
    
    Time: 6.117 ms
    t=# select * from test;
     name
    ------
     b
     blah
    (2 rows)
    

    https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

    By default, PL/pgSQL will report an error if a name in a SQL statement could refer to either a variable or a table column. You can fix such a problem by renaming the variable or column, or by qualifying the ambiguous reference, or by telling PL/pgSQL which interpretation to prefer.

    and further - basically the whole link is about it.

    And yet - after demonstrating how particular task this can be easily done with plpgsql, I still quote namual:

    The simplest solution is to rename the variable or column. A common coding rule is to use a different naming convention for PL/pgSQL variables than you use for column names. For example, if you consistently name function variables v_something while none of your column names start with v_, no conflicts will occur.