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
