How do I retrieve columns inserted in subquery 1, and return them with columns inserted in subquery 2? 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,       &lt;------ this
  l.data           &lt;------ 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.&lt;anonymous&gt; (/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).
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!