Re: Missing table from in INSERT RETURNING - Mailing list pgsql-general

From John McKown
Subject Re: Missing table from in INSERT RETURNING
Date
Msg-id CAAJSdjh4Hk-feWcbPkVb__sH+6wWeR5qgVrLnGYFGrs6BaNkRg@mail.gmail.com
Whole thread Raw
In response to Missing table from in INSERT RETURNING  (Rémi Cura <remi.cura@gmail.com>)
Responses Re: Missing table from in INSERT RETURNING  (John McKown <john.archie.mckown@gmail.com>)
Re: Missing table from in INSERT RETURNING  (David G Johnston <david.g.johnston@gmail.com>)
List pgsql-general
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.

On Tue, Feb 17, 2015 at 10:18 AM, Rémi Cura <remi.cura@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

pgsql-general by date:

Previous
From: Guillaume Drolet
Date:
Subject: Re: Starting new cluster from base backup
Next
From: John McKown
Date:
Subject: Re: Missing table from in INSERT RETURNING