Re: INSERT ... ON CONFLICT DO SELECT [FOR ...] take 2

On Tue, Dec 3, 2024, at 09:52, Andreas Karlsson wrote:
> Hi,
>
> Here is an updated version of the patch which fixes a few small bugs,
> including making sure it checks the update permission plus a bug found
> by Joel Jacobsson when it was called by SPI.

+1 for this feature.

This seems especially useful when designing idempotent APIs.
Neat to only need a single statement, for what we
currently need two separate statements for.

Here is an attempt of a realistic example:

CREATE OR REPLACE FUNCTION get_or_create_license_key(_user_id bigint, _product_id bigint)
RETURNS UUID BEGIN ATOMIC
INSERT INTO licenses (user_id, product_id)
VALUES (_user_id, _product_id)
ON CONFLICT (user_id, product_id) DO NOTHING;
SELECT license_key
FROM licenses
WHERE user_id = _user_id
AND product_id = _product_id;
END;

This can be simplified into:

CREATE OR REPLACE FUNCTION get_or_create_license_key(_user_id bigint, _product_id bigint)
RETURNS UUID BEGIN ATOMIC
INSERT INTO licenses (user_id, product_id)
VALUES (_user_id, _product_id)
ON CONFLICT (user_id, product_id) DO SELECT RETURNING license_key;
END;

I've tested the patch successfully and also looked at the code briefly
and at first glance think it looks nice and clean.

/Joel