CODEWITHSUNDEEP
postgresql
Camilo Sampedro
Camilo Sampedro

Reputation: 1436

How to store a constant value in a Postgresql script

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?

Upvotes: 0

Views: 1498

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 246288

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'

Upvotes: 3

Jeremy
Jeremy

Reputation: 6723

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;

Upvotes: 1

Lucas
Lucas

Reputation: 630

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));

Upvotes: 1

Related Questions