Re: 12.4 -> 12.5 upgrade, broken CTE query

Jason Ayre <jasonayre(at)gmail(dot)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