Save Variables in SQL

Here's how to save variables in sql for use later in the query.

A with statement will provide the key feature. Make a selection (rows) and projection (columns), and you can bind that 2D result to a name.

Only a single with statement is allowed in an sql script. You can create multiple variables by separating the values with commas.

There's an example script below that creates a permission row, n user rows and then n mapping table rows to relate the two.

There are two names bound: permission_id and user_ids.

The insert statement returns an id column for the one inserted row. This means that permission_id will be that scalar id value.

The second insert returns a selection of the 4 user ids. It is saved as user_ids. It is selected from later, when inserting user_perms, along with the permission_id, which is the same on every row.

begin;

with permission_id as (
  insert into perms (slug) 
  values ('release_window_edit') 
  returning id
),
user_ids as (
  insert into users (email) 
  values 
    ('leaning@example.com'),
    ('tower@example.com'),
    ('cheese@example.com'),
    ('wiz@example.com')
  returning id
)

insert into users_perms (user_ids, perms_id)
select u.id, p.id
from user_ids u, permission_id p;

commit;

Really nice, right! You don't have to re-select because of the insert returning clauses. And you don't have to select these things multiple times because you've preserved the values in the with statement.