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