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
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