Hey folks, thanks for the answers. As you guessed it is just a synthetic example, (so of course RETURNING some_value / 10 is possible in this case, but not in general when the function is much more complicated than "/10"). Same wise, adding a column is just not a serious option.
It correspond to a real need that is that you have rows associated to an *id*. Now you want to insert part of this row into a table with a serial field ( *gid*). Upon insertion, the serial field is automatically filled, and you get it with a returning statement. The problem is that you have no way to know which value of *gid* is associated to which *id*. The other workaround I found is to get nextvalue() before insert to to know beforehand what will be the *(gid, id)* association. It is suboptimal and ugly, so I would prefer another solution. Cheers, Rémi-C 2015-02-17 21:33 GMT+01:00 John McKown <john.archie.mck...@gmail.com>: > On Tue, Feb 17, 2015 at 2:15 PM, David G Johnston < > david.g.johns...@gmail.com> wrote: > >> On Tue, Feb 17, 2015 at 1:08 PM, John McKown [via PostgreSQL] <[hidden >> email] <http:///user/SendEmail.jtp?type=node&node=5838309&i=0>> wrote: >> >>> I haven't seen any one else reply. I don't know if you've gotten a >>> solution. But the following seemed to work for me: >>> >>> >> mine apparently got bounced... >> >> >> >>> WITH serie AS ( >>> select s, s*10 as computing >>> from generate_series(1,10) as s >>> ) >>> INSERT INTO test_insert_returning (some_value) >>> SELECT computing >>> FROM serie >>> RETURNING gid, some_value; >>> >> >> or, "RETURNING some_value / 10" >> >> >>> From my reading on the RETURNING phrase, you can only return values from >>> the table into which you are doing the INSERT. Not any other table or view >>> which might be referenced. >>> >>> >> This is correct; and I am curious on the use case that requires >> otherwise. >> > > A weird one might be where in data available ("s") in the CTE is in > English measure (feet, miles, etc) and the OP wants to insert the > equivalent Metric value ("computing") into the table, but needs to return > the English value to the caller (why?). He does not want to put the English > measure into the table itself, just to be able to return it. And not need > to do a reverse conversion. As I said, just a weird thought. From a > effervescent fount of weird thoughts - me. Or perhaps what he is storing in > the table is a one-way hash of a password, and wants to return the > clear-text password to the caller? Hum, that is almost reasonable. I'll > need to be more on guard. > > > >> >> David J. >> >> > > > -- > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown >
