Return value cross join

问题:

I have two tables, one is a table #1 contains user information, email, password, etc..

the other table #2 contains item information

when I do a insert into table #2, and then use the returning statement, to gather what was inserted (returning auto values as well as other information), I also need to return information from table #1.

(excuse the syntax)

example:

insert into table #1(item,user) values('this item','the user') 
returning *, select * from table 2 where table #1.user = table #2.user)

in other words, after the insert I need to return the values inserted, as well as the information about the user who inserted the data.

is this possible to do?

the only thing I came up with is using a whole bunch of subquery statements in the returning clause. there has to be a better way.

回答1:

I suggest a data-modifying CTE (Postgres 9.1 or later):

WITH ins AS (
   INSERT INTO tbl1(item, usr)
   VALUES('this item', 'the user') 
   RETURNING usr
   )
SELECT t2.*
FROM   ins
JOIN   tbl2 t2 USING (usr)

Working with the column name usr instead of user, which is a reserved word.

回答2:

Use a subquery.

Simple demo: http://sqlfiddle.com/#!15/bcc0d/3

insert into table2( userid, some_column )
values( 2, 'some data' )
returning 
    userid, 
    some_column,
    ( SELECT username FROM table1
      WHERE table1.userid = table2.userid
     );