Re: Missing table from in INSERT RETURNING

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(dot)archie(dot)mckown(at)gmail(dot)com>:

> On Tue, Feb 17, 2015 at 2:15 PM, David G Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)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
>