How to store a constant value in a Postgresql script

Question

I need to insert a row in a table, generating its key with uuid_generate_v4(), and I need to use that key as a foreign key in a subsequent insert in another table, like:

\set generated_id uuid_generate_v4()
INSERT INTO
 table1(id)
 VALUES(:generated_id);
INSERT INTO
 table2(id, table1_id)
 VALUES(uuid_generate_v4(), :generated_id);

But when I read :generated_id, every time it gives me a different value, like it is computing it again each time I try to read it.

Is it possible to ask \set to not re-compute uuid_generate_v4() again? How could this be achieved otherwise?

• jjanes: \set doesn't compute or re-compute anything. It stores the function name as text: it is when this function name gets textually substituted into a string and then that string is executed as a query that it gets computed. You need \gset which will actually invoke the function and store the results (as text).

Answer 1

You can use psql's \gset to set a variable to the result of a query:

SELECT uuid_generate_v4() AS generated_id \gset

That will set the variable generated_id.

To use the variable in your script, quote it like this:

:'generated_id'

• Laurenz Albe: I added something to the answer.

Answer 2

Another solution may be to create a temporary table to store that value

-- generate id and store it in a temporary table
CREATE TEMPORARY TABLE tmpval(generated_id uuid);
INSERT INTO tmpval VALUES (uuid_generate_v4());
-- run inserts referencing this table value
INSERT INTO
 table1(id)
 VALUES((SELECT generated_id FROM tmpval));
INSERT INTO
 table2(id, table1_id)
 VALUES(uuid_generate_v4(), (SELECT generated_id FROM tmpval));

Answer 3

Yet another solution is to just do it in one statement:

WITH t1_insert AS (
 INSERT INTO table1(id) SELECT uuid_generate_v4() RETURNING id
)
INSERT INTO table2(id, table1_id)
SELECT uuid_generate_v4(), t1_insert.id
FROM t1_insert;