How to store a return value of an SQL query inside a variable-CodePudding

Given a users and a roles table, I want to write an SQL statement, which inserts a new row into the users table and associates a row of the roles table with it.

This is what I have tried:

INSERT INTO users(firstname, lastname) VALUES ('John', 'Smith') RETURNING id;

INSERT INTO roles(role, user_id)
VALUES ('ROLE_USER', id);

The id used in the last line is not yet associated with the return value of the first line. Can someone explain to me how I could store the return type of the first line inside a variable and then use it in my last line?

I have come across the DECLARE keyword, but I am not sure this works with every kind of database. I am using a postgres DB. When using DECLARE @VariableName, the @ gets marked as wrong syntax in my db-migrate script.

CodePudding user response：

You can use a data modifying CTE:

with new_user as (
  INSERT INTO users(firstname, lastname) 
  VALUES ('John', 'Smith') 
  RETURNING id
)
INSERT INTO roles(role, user_id)
SELECT 'ROLE_USER', id
FROM new_user;