CODEWITHSUNDEEP
sqlpostgresql
Tamás Polgár
Tamás Polgár

Reputation: 2262

How to pass a return value from one PostgreSQL query to another?

I have two queries. The first creates a new user. The table has three fields, id, name and email. The id field is an autoincrement value.

The second query creates additional data related to this user, using the ID value.

How can I pass the RETURNING value of the first to the second query?

Example:

INSERT INTO users (
  name, 
  email
) VALUES (
  'John Doe',
  '[email protected]'
) RETURNING id;

INSERT INTO users_stuff (
  user_id,
  money,
  hasCar
) VALUES (
  id,
  1000,
  false
);

The above example doesn't work because the second query never receives the first one's RETURNING value, which would be the new record's autoincrement ID.

Is there a way though?

Upvotes: 1

Views: 751

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use a CTE:

WITH u AS (
      INSERT INTO users (name, email)
          VALUES ('John Doe', '[email protected]')
          RETURNING id;
     )
INSERT INTO users_stuff (user_id, money, hasCar) 
    SELECT u.id, 1000, false
    FROM u;

Upvotes: 2

Related Questions