BUG #3320: Error when using INSERT...RETURNING as a subquery
The following bug has been logged online:
Bug reference: 3320
Logged by: Jan Szumiec
Email address: jan.szumie@infiniteloop.eu
PostgreSQL version: 8.2.4
Operating system: Windows XP
Description: Error when using INSERT...RETURNING as a subquery
Details:
Having:
CREATE TABLE efforts
(
id serial NOT NULL,
effort integer
)
WITHOUT OIDS;
CREATE TABLE items
(
id serial NOT NULL,
"type" character varying(255),
created_at timestamp without time zone,
subject character varying(255),
body text,
effort integer,
CONSTRAINT items_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
executing the following query:
UPDATE items SET (type, post_id) = (INSERT INTO efforts (effort) VALUES
(667) RETURNING 'Item', id) WHERE id = 1937
produces this error:
ERROR: syntax error at or near "INTO"
SQL state:42601
Character:44
"Jan Szumiec" <jan.szumie@infiniteloop.eu> writes:
UPDATE items SET (type, post_id) = (INSERT INTO efforts (effort) VALUES
(667) RETURNING 'Item', id) WHERE id = 1937
Sorry, RETURNING is only supported at the top level of a query.
regards, tom lane
On Tue, May 29, 2007 at 09:41:38AM -0400, Tom Lane wrote:
"Jan Szumiec" <jan.szumie@infiniteloop.eu> writes:
UPDATE items SET (type, post_id) = (INSERT INTO efforts (effort) VALUES
(667) RETURNING 'Item', id) WHERE id = 1937Sorry, RETURNING is only supported at the top level of a query.
What would be involved with making this possible? What we have at the
moment is a pretty clear POLA violation because unlike the rest of the
row-returning objects (tables, views, SRFs and VALUES() clauses), only
RETURNING can't be used in a subquery.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
"David Fetter" <david@fetter.org> writes:
On Tue, May 29, 2007 at 09:41:38AM -0400, Tom Lane wrote:
"Jan Szumiec" <jan.szumie@infiniteloop.eu> writes:
UPDATE items SET (type, post_id) = (INSERT INTO efforts (effort) VALUES
(667) RETURNING 'Item', id) WHERE id = 1937Sorry, RETURNING is only supported at the top level of a query.
What would be involved with making this possible? What we have at the
moment is a pretty clear POLA violation because unlike the rest of the
row-returning objects (tables, views, SRFs and VALUES() clauses), only
RETURNING can't be used in a subquery.
It has the same problem that SELECT triggers have. How many rows should you
expect that subquery to insert, update, or delete if it's used in a join
clause? Or in the where clause of another insert/update/delete statement?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
On Tue, 2007-05-29 at 18:10 +0100, Gregory Stark wrote:
It has the same problem that SELECT triggers have. How many rows should you
expect that subquery to insert, update, or delete if it's used in a join
clause? Or in the where clause of another insert/update/delete statement?
We could handle it essentially like a volatile set-returning function.
It may be easy to shoot oneself in the foot, but that is true for many
uses of volatile functions.
If the argument is that we shouldn't make it any easier, that's a fair
point, but this is one possible definition.
Regards,
Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
On Tue, 2007-05-29 at 18:10 +0100, Gregory Stark wrote:
It has the same problem that SELECT triggers have. How many rows should you
expect that subquery to insert, update, or delete if it's used in a join
clause? Or in the where clause of another insert/update/delete statement?
We could handle it essentially like a volatile set-returning function.
Uh-huh. Please provide a concise, accurate definition of what that
does. For extra points, be sure it describes the behavior of all recent
Postgres versions. (And after that, we could argue about whether we
actually *like* the described behavior ... which I'll bet we won't.)
regards, tom lane
On Tue, 2007-05-29 at 22:41 -0400, Tom Lane wrote:
Jeff Davis <pgsql@j-davis.com> writes:
On Tue, 2007-05-29 at 18:10 +0100, Gregory Stark wrote:
It has the same problem that SELECT triggers have. How many rows should you
expect that subquery to insert, update, or delete if it's used in a join
clause? Or in the where clause of another insert/update/delete statement?We could handle it essentially like a volatile set-returning function.
Uh-huh. Please provide a concise, accurate definition of what that
does. For extra points, be sure it describes the behavior of all recent
Postgres versions. (And after that, we could argue about whether we
actually *like* the described behavior ... which I'll bet we won't.)
I understand that we don't make many guarantees about when and how many
times volatile functions are executed (the most obvious example is the
WHERE clause).
I also understand the argument that we don't want to extend that
uncertainty to UPDATE ... RETURNING.
It is possible to define behavior though, because it's already done for
volatile functions. Even if it's not a good definition, and even if that
definition changes between versions and is non-deterministic, it seems
like it offers some kind of starting place.
Regards,
Jeff Davis