On Tue, Feb 17, 2015 at 2:07 PM, John McKown <john.archie.mck...@gmail.com>
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:
>
> 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;
>
> 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.
>
OOPS, I see what I did wrong. You wanted the "s" value from serie and my
example showed the other value from serie. My apologies. Why not insert the
"s" value into a third column in "test_insert_returning"? That is:
CREATE TABLE test_insert_returning(
gid SERIAL,
s_temp integer,
some_value int
);
WITH serie AS (
SELECT s, s*10 as computing
FROM generate_series(1,10) as s
)
INSERT INTO test_insert_returning(some_value,s)
SELECT computing, s
FROM serie
RETURNING gid, s_temp
;
You end up getting what is desired, at the cost of a "junk" column in your
table.
>
> On Tue, Feb 17, 2015 at 10:18 AM, Rémi Cura <remi.c...@gmail.com> wrote:
>
>> Hello dear list,
>> I would appreciate some help on a small matter that has been bothering me
>> for a long time :
>>
>>
>> CREATE TABLE test_insert_returning(
>> gid SERIAL
>> ,some_value int
>> );
>> 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, serie.s
>>
>> doesn't work.
>>
>> The only workaround I found was to create a plpgsql function that doesan
>> idnividual insert
>> so that :
>> WITH serie AS (
>> select s, s*10 as computing
>> from generate_series(1,10) as s
>> )
>> SELECT serie.s, my_inserting_function(computing)
>> FROM serie ;
>>
>> But it is very annoying and potentially bad for performance because many
>> insert may be fired.
>>
>> Any solution?
>> (postgres 9.3)
>> Cheers,
>> Rémi-C
>>
>>
>
>
> --
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>
--
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
