Thread: 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 = 1937
>
> Sorry, 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 = 1937
>>
>> Sorry, 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