Re: Missing table from in INSERT RETURNING

John McKown <john.archie.mckown@xxxxxxxxx> · Tue, 17 Feb 2015 14:07:44 -0600

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@xxxxxxxxx> 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