Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14

Поиск
Список
Период
Сортировка
От Gavin Roy
Тема Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14
Дата
Msg-id CAFVAjJHYveNgZyy1ALR3UquQOmL2WnEK2eyabvFocr8RnHYFrA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
Дерево обсуждения


On Thu, Oct 7, 2021 at 2:54 PM Adrian Klaver <adrian.klaver@aklaver.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

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14
Следующее
От: Gavin Roy
Дата:
Сообщение: Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14