INSERT ... ON CONFLICT () SELECT

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
<https://www.postgresql.org/message-id/19806.1468429941@sss.pgh.pa.us> 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