How do I retrieve columns inserted in subquery 1, and return them with columns inserted in subquery 2?

Question

Stack:

React Native
Node 10.x
pg-promise 8.x
PostgreSQL 10.x

A location row is upserted before creating a user row:

const insertUserSql = `
  with l as (
  insert into locations (
    data,
    placeid,
    user_count
  ) values (
    $1,
    $2,
    1
  )
  on conflict
  on constraint placeid
  do update set
    user_count = locations.user_count + 1
  returning
    placeid, data, uuid
)
insert into users as u (
  password,
  email,
  first_name,
  last_name,
  location_uuid
) VALUES (
  crypt(
    $3,
    gen_salt('bf')
  ),
  $4,
  $5,
  $6,
  (select uuid from l)
)
RETURNING
  u.uuid,
  u.email,
  u.first_name,
  u.last_name,
  u.created,
  u.active,
  u.last_login,
  u.location_uuid
`;

In the insert user subquery I want to return the location table columns placeid, data

However, if I append:

...
RETURNING
  u.uuid,
  u.email,
  u.first_name,
  u.last_name,
  u.created,
  u.active,
  u.last_login,
  u.location_uuid,
  l.placeid,       <------ this
  l.data           <------ this

I receive the following error:

 name:  error 
 message:  missing FROM-clause entry for table "l" 
 code:  42P01 
 stack error: missing FROM-clause entry for table "l"
    at Connection.parseE (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:378:19)
    at Socket.<anonymous> (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)
    at readableAddChunk (_stream_readable.js:264:11)
    at Socket.Readable.push (_stream_readable.js:219:10)
    at TCP.onread (net.js:639:20)

I don't want to make two "trips" to the DB (which would be effective but inefficient).

score 1 · Answer 1 · edited Oct 02 '18 at 16:26

I figured it out:

You can only return fields affected by the insert (thus the error in subquery 2 return statement)

I needed to move the subquery into a CTE and then select the data I want to return

const insertUserSql = `
  with l as (
  insert into locations (
    data,
    placeid,
    user_count
  ) values (
    $1,
    $2,
    1
  )
  on conflict
  on constraint placeid
  do update set
    user_count = locations.user_count + 1
  returning
    placeid, data, uuid
), cu as (
  insert into users as u (
    password,
    email,
    first_name,
    last_name,
    location_uuid
  ) VALUES (
    crypt(
      $3,
      gen_salt('bf')
    ),
    $4,
    $5,
    $6,
    (select uuid from l)
  )
  RETURNING
    u.uuid,
    u.email,
    u.first_name,
    u.last_name,
    u.created,
    u.active,
    u.last_login,
    u.location_uuid
)
select
  l.data,
  l.placeid,
  cu.email,
  cu.uuid,
  cu.first_name,
  cu.last_name,
  cu.created,
  cu.active,
  cu.last_login,
  cu.location_uuid
  from l, cu
`;

However if there is a more efficient way to do this, let me know!

score 0 · Answer 2 · answered Oct 03 '18 at 15:01

0

Your second insert looks independent of the first insert, which means you can have each insert do RETURNING * in the end, and execute the whole query via method multi, and then get the result of both queries.

answered Oct 03 '18 at 15:01

vitaly-t

133
7

How do I retrieve columns inserted in subquery 1, and return them with columns inserted in subquery 2?

2 Answers2