Set variables for postgres initial setup script

Question

I have created the following setup script. The script is meant to be run before the application (which would run on node) is deployed. The idea is that it would eventually contain all table definitions, relations, stored procedures and some seed data to make the application work, split into different files. But currently I'm stuck because I can't figure out how I can store data in variables. In this example we have profiles (because user is a keyword) and inventories. I want each user to have an inventory. For this to work I need to generate an inventory, store its id in a variable and pass it to the profile.
But for some reason postgres won't allow me to declare variables. Heres what I got so far:

drop database test;
create database test;
\c test

create extension if not exists "uuid-ossp";    

create table inventory(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text
);

create table profile(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text,
    inventory_id uuid references inventory(id)
);

DECLARE myid uuid;

/*I want to set the inv_id to the result of this expression*/
insert into inventory(name) values('asdf') returning id into myid;


insert into profile(name,inventory_id) values ('user1',@myid)

But I get the following error:

$ psql -U postgres -f init.sql    
psql:init.sql:18: ERROR:  syntax error at or near "uuid"
LINE 1: DECLARE myid uuid;
                     ^

So how can I create a variable to store this id? Am I doing something wrong in general, because I'm pretty sure Declare is part of the SQL spec.

postgresql.org/docs/11/sql-declare.html Declare is part of the SQL spec.Yes, but it has a different meaning from what you think it has. — wildplasser
– wildplasser, Commented Jan 1, 2021 at 17:57

Bergi · Accepted Answer · 2021-01-01 18:05:10Z

1

You can use

WITH myids AS (
  INSERT INTO inventory(name)
  VALUES ('asdf')
  RETURNING id
)
INSERT INTO profile(name,inventory_id)
SELECT 'user1', id
FROM myids;

answered Jan 1, 2021 at 18:05

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2741831 Over a year ago

thanks, this works really well. But since myids can contain multiple records,Is there a trick so I can use this to create multiple profiles with associated inventories at once?

Bergi Over a year ago

You can insert multiple values at once with VALUES, and you can do an arbitrary JOIN in the select clause. If your inventory names are unique, it's trivial to relate them, otherwise you'll have to pull some tricks with row_number order.

Collectives™ on Stack Overflow

Set variables for postgres initial setup script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related