Variables in PostgreSQL with UUID

Question

All my tables use UUID primary keys. I want to fill tables with test data in one sql file, but can't use UUID PK as FK in other tables, because default function uuid_generate_v4() generates a random value during execution.

Example what I want:

SET Uservar = uuid_generate_v4();
SET Postvar = uuid_generate_v4();

INSERT INTO public."User" (id, name) VALUES (Uservar, 'Foo Bar');
INSERT INTO public."Post" (id, content, userId) VALUES (Postvar, 'Test message', Uservar)

How to do this? Or how to select already created UUID and store for next insert?

Answer 1

E.g. say you had table like this:

create table my_table(uuid_column uuid PRIMARY KEY NOT NULL);

You can insert a variablized uuid like so:

DO $$
DECLARE
    my_uuid uuid = uuid_generate_v4();
BEGIN
    insert into my_table (uuid_column) values (my_uuid);
    select * from my_table where uuid_column = my_uuid;
END $$;

Check this documentation.

N.B. To have uuid_generate_v4() available, make sure you have the below snipped ran before your use it:

 CREATE EXTENSION IF NOT EXISTS "uuid-ossp";