join value in a RETURNING which is not inserted

I'm doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert. Is it possible? I get a "column doesn't exist" error.

INSERT INTO new_table (x,y) 
select A.x,B.y 
from A 
  join B on A.w = B.z 
RETURNING id,B.z;

The new_table has a unique constraint on (x,y).

The new_id and B.z are needed to insert into a second table.

Perhaps there's a better alternative but I can only think of joining back to the 2 tables.

This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable:

with ins (id, x, y) as
( insert into new_table (x, y) 
  select A.x, B.y 
  from A join B on A.w = B.z 
  returning id, x, y
)
-- insert into another_table (id, z)
select 
    ins.id, B.z        -- whatever columns from the 3 tables
from ins 
  join A on A.x = ins.x 
  join B on B.y = ins.y and A.w = B.z ;

StackExchange Database Administrators Q#90211, answer score: 13