Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


begin;
create temp table firms(
        name text
        ,firm_id bigint  primary key generated always as identity
);

create temp table users(
    email text
	,admin boolean 
    ,active_stash bigint
   ,user_id  bigint primary key generated always as identity
	,firm_id bigint
	,constraint fk_firms foreign key(firm_id)
	references firms(firm_id)
);

create temp table stashes(
    new_stash_id bigint generated always as identity
    ,name text
    ,user_id bigint
	,constraint fk_users foreign key(user_id)
	references users(user_id)
);
commit;
create or replace procedure test()
language plpgsql
as $$
declare
	_new_stash_id bigint;
	_new_user_id bigint;
begin
	WITH insert1 AS (
		INSERT INTO firms (name) VALUES ('Jack_testFirm') RETURNING firm_id  AS ret_firm_id
	),
	insert2 AS (
	INSERT INTO users (email, admin, firm_id) 
	SELECT 'jack@gmail.com', TRUE, ret_firm_id  FROM insert1
	RETURNING users.user_id AS new_user_id
	)
	,
	insert3 AS (
	INSERT INTO stashes (name, user_id)
	SELECT 'Stash 1', new_user_id FROM insert2
	RETURNING new_stash_id, user_id
	)
	select new_stash_id, user_id  
	from insert3  into _new_stash_id, _new_user_id;

	raise NOTICE '_new_stash_id: %', _new_stash_id;
	raise NOTICE '_new_user_id: %',_new_user_id;

	UPDATE users
	SET active_stash = _new_stash_id
	where user_id = _new_user_id;
end
$$;
call test();
table users;


email
admin
active_stash
user_id
firm_id


jack@gmail.com
t
1
1
1


table firms;


name
firm_id


Jack_testFirm
1


table stashes; 


new_stash_id
name
user_id


1
Stash 1
1