How to store a return value of an SQL query inside a variable
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.
Solution – 1
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;