Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE b (
    b_uuid int PRIMARY KEY,
    b_name text
);

INSERT INTO b VALUES (1, 'XYZ');

CREATE TABLE a (
    a_uuid int PRIMARY KEY,
    b_uuid int REFERENCES b(b_uuid) 
);

CREATE OR REPLACE FUNCTION gen_random_uuid() RETURNS int AS $$
BEGIN
    RETURN trunc(random() * 3) + 1;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE



INSERT 0 1



CREATE TABLE



CREATE FUNCTION


WITH input (b_uuid, b_name) AS (
    VALUES (
        gen_random_uuid(), 'ABC'
    )
), ins_b AS (
    INSERT INTO b (
        b_uuid, b_name
    )
    TABLE input
    ON CONFLICT DO NOTHING
    RETURNING b_uuid
), new_b AS (
    TABLE ins_b
    UNION ALL
    SELECT b.b_uuid FROM input
    JOIN b USING (b_uuid)
)
INSERT INTO a (a_uuid, b_uuid)
VALUES (
    gen_random_uuid(), (SELECT b_uuid FROM new_b)
);

INSERT 0 1


SELECT * FROM b;


b_uuid
b_name


1
XYZ

3
ABC



SELECT 2


SELECT * FROM a;


a_uuid
b_uuid


2
3



SELECT 1