Execute \set command with function as value

Question

In one of my SQL scripts for PostgreSQL I want to declare a value so that I can use it in several places in the rest of the script.

Here is what I did based on the following thread :

\set cat_id uuid_generate_v4()
insert into categories (id, name) values (:cat_id, 'Category 1')
insert into other_table (id, category_id) values (uuid_generate_v4(), :cat_id)

The problem is that the variable cat_id does not take the value once for all. It just replaces :cat_id by uuid_generate_v4(). Therefore, the value is not the same in the two insert queries.

How can I do to give cat_id the value of the execution of the function ?

Thanks.

score 3 · Accepted Answer · answered Aug 09 '18 at 14:46

When :cat_id contains uuid_generate_v4(), you're using it as a macro. \set variables in psql can be used both as macros and as variables.

Q: How can I do to give cat_id the value of the execution of the function ?

select uuid_generate_v4() as cat_id \gset

Doc:

\gset [ prefix ]
Sends the current query input buffer to the server and stores the query's output into psql variables (see Variables). The query to be executed must return exactly one row.
Each column of the row is stored into a separate variable, named the same as the column.

Note that afterwards to inject it into a statement, the syntax to use is :'cat_id' so that it gets properly quoted as a literal.

score 2 · Answer 2 · answered Aug 09 '18 at 11:53

You can use an anonymous DO block with a variable initialized with a new UUID.

DO
$$
DECLARE
  cat_id uuid = uuid_generate_v4();
BEGIN
  INSERT INTO categories
              (id,
               name)
              VALUES (cat_id,
                      'Category 1');
  INSERT INTO other_table
              (id,
               category_id)
              VALUES (uuid_generate_v4(),
                      cat_id),
END;
$$
LANGUAGE PLpgSQL;

Execute \set command with function as value

2 Answers2