Обсуждение: Missing table from in INSERT RETURNING
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
(postgres 9.3)
Cheers,
Rémi-C
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 computingfrom generate_series(1,10) as s)INSERT INTO test_insert_returning (some_value)SELECT computingFROM serieRETURNING gid, serie.sdoesn't work.The only workaround I found was to create a plpgsql function that doesan idnividual insertso that :WITH serie AS (select s, s*10 as computingfrom 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
John McKown
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 computingfrom generate_series(1,10) as s)INSERT INTO test_insert_returning (some_value)SELECT computingFROM serieRETURNING 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.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 computingfrom generate_series(1,10) as s)INSERT INTO test_insert_returning (some_value)SELECT computingFROM serieRETURNING gid, serie.sdoesn't work.The only workaround I found was to create a plpgsql function that doesan idnividual insertso that :WITH serie AS (select s, s*10 as computingfrom 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-CHe's about as useful as a wax frying pan.10 to the 12th power microphones = 1 MegaphoneMaranatha! <><
John McKown
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
John McKown
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 computingfrom generate_series(1,10) as s)INSERT INTO test_insert_returning (some_value)SELECT computingFROM serieRETURNING 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.
David J.
View this message in context: Re: Missing table from in INSERT RETURNING
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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 computingfrom generate_series(1,10) as s)INSERT INTO test_insert_returning (some_value)SELECT computingFROM serieRETURNING 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
John McKown
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").thanks for the answers.
As you guessed it is just a synthetic example,
Same wise, adding a column is just not a serious option.
The problem is that you have no way to know which value of gid is associated to which id.
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.mckown@gmail.com>:
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 computingfrom generate_series(1,10) as s)INSERT INTO test_insert_returning (some_value)SELECT computingFROM serieRETURNING 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 MegaphoneMaranatha! <><
John McKown