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.
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;