3

Breaking it down to it's most simplest form... Is it possible to use INSERT INTO with a RETURNING clause to capture a non-existing column alongside the output? 

test=# \d x
       Table "public.x"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer | 

--void column does not exist in table...    
INSERT INTO x (a, void) values (1,2),(3,4) RETURNING a, void;

Under this scheme would return:

 a | void 
---+---
 1 | 2
 3 | 4

To give this problem some context...

I have a client ID that I'd like to append to the results so that it's server (real) ID will be returned alongside as a pair.

I don't actually need the client ID persisted to the DB.

I could use the AS keyword in the output, but there is no way to diversify the client ID with this method:

test=# INSERT INTO x (a, b) VALUES (1,2),(1,4) RETURNING a, b, 6 as clientid;
 a | b | clientid 
---+---+----------
 1 | 2 |        6
 1 | 4 |        6
(2 rows)

INSERT 0 2

I tried playing with some CTE's, can't seem to get it. Basically, I want to be able to provide a ROW or, array of IDs that I want JOINed to the result.

In theory, I could do this at the server level instead of DB level. Just curious.

Jordan Arsenault
  • 355
  • 1
  • 3
  • 12
  • You can use CTEs and then join the result of CTE a (the insert) with another CTE b (that provides a list of values). – ypercubeᵀᴹ Dec 02 '15 at 07:39
  • 1
    It's not very clear though, who will provide these client values? Will/can they be part of the values that are inserted? Like this for example? with v (a,b,clientid) as (values (1,2,6), (3,4,7)), i as (insert into x (a,b) select a,b from v returning a,b) select * from v ; Or more complicated (with a join) so it can show additional columns from the inserted i table: with v (a,b,clientid) as (values (1,2,6), (3,4,7)), i as (insert into x (a,b) select a,b from v returning *) select i.*,v.clientid from v join i using (a,b) ; – ypercubeᵀᴹ Dec 02 '15 at 07:45
  • @ypercube, awesome! your first solution works. Basically the client ID values will be provided at the server level that is producing the SQL string when building up the VALUES( (...),(...) ). Does it make sense? Please post your comment as an answer and I'm glad to accept. – Jordan Arsenault Dec 02 '15 at 19:15
  • I spoke too soon -- using your first solution, I was unable to return the server (real) id in the RETURNING clause, or any other column not present in the statements' column list. (I'm sure there is a way around this). So, I resorted to your second solution and I'm able to return both ids in the response. Excellence. – Jordan Arsenault Dec 02 '15 at 19:49
  • 1
    yes, if you want other columns from the inserted table (like a serial), the 2nd query works. – ypercubeᵀᴹ Dec 02 '15 at 21:42

0 Answers0