pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jason Ayre <jasonayre@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: 12.4 -> 12.5 upgrade, broken CTE query
Date: Wed, 10 Mar 2021 16:51:31 -0500
Message-ID: <2218080.1615413091@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJ7aF3ytf3q0YxGvw+t7yG_JUbFEL5uvdkV8AC3=3Sr-cXaOng@mail.gmail.com>
References: <CAJ7aF3ytf3q0YxGvw+t7yG_JUbFEL5uvdkV8AC3=3Sr-cXaOng@mail.gmail.com>

Jason Ayre <jasonayre@gmail.com> writes:
> RDS Auto updated my instanced from 12.4 to 12.5 last night, and the
> following query (and several others like it) which were previously working
> throws a "set-returning functions must appear at the top level of FROM" -

> ERROR:  set-returning functions must appear at top level of FROM
> LINE 2:           FROM surveys, uuid(jsonb_array_elements_tex...

That restriction's been there since v10, so I'm having a hard time
believing that this query worked in 12.4.

It looks to me like the easiest way to get around it is just to put
the uuid() call in the SELECT output list:

WITH "questions_order" AS (
      SELECT uuid(question_id) AS question_id, sort_position
          FROM surveys,
          jsonb_array_elements_text(surveys.custom_fields->'questions_order')
          WITH ORDINALITY AS f(question_id, sort_position)
          ...

In other cases it might be best to stick the extra function into
an additional layer of lateral function, like

          FROM surveys,
          jsonb_array_elements_text(surveys.custom_fields->'questions_order')
          WITH ORDINALITY AS f(question_id, sort_position),
          uuid(f.question_id) AS whatever

			regards, tom lane





view thread (2+ messages)

Message-ID: <2218080.1615413091@sss.pgh.pa.us>
Permalink:  ../2218080.1615413091@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2218080.1615413091@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jasonayre@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: 12.4 -> 12.5 upgrade, broken CTE query
  In-Reply-To: <2218080.1615413091@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox