From: | Gavin Roy <gavinr(at)aweber(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14 |
Date: | 2021-10-07 19:05:27 |
Message-ID: | CAFVAjJHYveNgZyy1ALR3UquQOmL2WnEK2eyabvFocr8RnHYFrA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Oct 7, 2021 at 2:54 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 10/7/21 11:38 AM, Gavin Roy wrote:
> > Hi All,
> >
> > My team was testing against Postgres 14 to ensure we could cleanly
> > upgrade and we ran across a regression in our PL/PGSQL code related to
> > the updates to RETURN QUERY.
> >
> > Our code which works in previous versions of Postgres uses UPDATE
> > RETURNING and INSERT RETURNING in combination with RETURN QUERY. It
> > appears that in the parallelism updates, RETURN QUERY now only accepts
> > SELECT queries.
>
> I'm pretty sure folks are going to want to see an example of the code
> and the errors thrown in version 14.
>
Sorry, I thought that was pretty clear. As an example, this worked prior to
14 and no longer works:
CREATE TABLE foo (
bar SERIAL PRIMARY KEY,
baz TEXT
);
CREATE FUNCTION update_foo(in_bar INT4, in_baz TEXT) RETURNS SETOF foo AS $$
BEGIN
RETURN QUERY UPDATE foo SET baz = in_baz WHERE bar = in_bar RETURNING
bar, baz;
END;
$$ LANGUAGE PLPGSQL;
postgres=# SELECT * FROM update_foo(1, 'baz?');
ERROR: query is not a SELECT
CONTEXT: query: UPDATE foo SET baz = in_baz WHERE bar = in_bar RETURNING
bar, baz
PL/pgSQL function update_foo(integer,text) line 3 at RETURN QUERY
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Roy | 2021-10-07 19:06:05 | Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14 |
Previous Message | Tom Lane | 2021-10-07 19:05:00 | Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14 |