Hello,
I am looking to add a new language feature that returns the rows that conflict on an INSERT, and would appreciate feedback and guidance on this.
Here is an example.
To implement a get_or_create_id() function, this is how it must currently be done:
CREATE TABLE example (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
value FLOAT);
CREATE UNIQUE INDEX ON example (name);
CREATE FUNCTION get_or_create_id(_name TEXT) RETURNS INT AS
$$
WITH get AS (
SELECT id FROM example WHERE name=_name
), new AS (
INSERT INTO example (name) VALUES (_name)
ON CONFLICT (name) DO NOTHING
RETURNING id
)
SELECT id FROM get
UNION ALL
SELECT id FROM new
$$
LANGUAGE sql;
SELECT get_or_create_id('foo'); -- 1
SELECT get_or_create_id('bar'); -- 2
SELECT get_or_create_id('foo'); -- 1
With the proposed "INSERT ... ON CONFLICT () SELECT" feature, the get_or_create_id() function is simplified to:
CREATE FUNCTION get_or_create_id(_name TEXT) RETURNS INT AS
$$
INSERT INTO example (name) VALUES (_name)
ON CONFLICT (name) DO SELECT
RETURNING id
$$
LANGUAGE sql;
In the case of a CONFLICT, the selected rows are exactly those same rows that would be operated on by an ON CONFLICT () DO UPDATE clause. These rows are then made available to the RETURNING clause in the same manner. Just like "DO NOTHING", the "DO SELECT" clause takes no arguments. It only makes the conflicting rows available to the RETURNING clause.
Tom Lane has previously
responded to a similar request which was ill-defined, especially in the context of exclusion constraints. I believe that by SELECTing exactly those same rows that an UPDATE clause would on a CONFLICT, this becomes well-defined, even with exclusion constraints.
Feedback/guidance is most welcome.
Best regards,
Matt