BUG #15960: ON CONFLICT Trying accessing to variables

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: anton(dot)dutov(at)gmail(dot)com
Subject: BUG #15960: ON CONFLICT Trying accessing to variables
Date: 2019-08-15 13:27:24
Message-ID: 15960-ea463c93a1f572db@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15960
Logged by: Anton Dutov
Email address: anton(dot)dutov(at)gmail(dot)com
PostgreSQL version: 11.3
Operating system: Linux
Description:

Using upsert in stored procedure ON CONFLICT(id, ...) trows "ERROR: column
reference "id" is ambiguous" where id used as column name and as variable,
but ON CONFLICT can't use variables

Reproduce

CREATE TABLE IF NOT EXISTS test_table (id INT PRIMARY KEY, note TEXT);

CREATE OR REPLACE FUNCTION test_table_add (
in_id INT
,in_note TEXT
,OUT id INT
) RETURNS INT LANGUAGE plpgsql VOLATILE SECURITY DEFINER AS $$
BEGIN
INSERT INTO test_table (id, note) VALUES (in_id, in_note)
ON CONFLICT (id) DO UPDATE SET note = in_note;
END;
$$;

SELECT test_table_add (1, 'ONE');

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2019-08-15 16:17:36 Re: BUG #15960: ON CONFLICT Trying accessing to variables
Previous Message PG Bug reporting form 2019-08-14 21:35:25 BUG #15959: 'DROP EXTENSION pglogical' while an unused logical replication slot exists causes slot corruption