12.4 -> 12.5 upgrade, broken CTE query
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" -
Query is used to order "questions" from a jsonb field on "survey"
containing "custom_fields.questions_order"
https://gist.github.com/jasonayre/54177560de0e77912bdefab965f6868d
WITH "questions_order" AS ( SELECT question_id, sort_position
FROM surveys,
uuid(jsonb_array_elements_text(surveys.custom_fields->'questions_order'))
WITH ORDINALITY AS f(question_id, sort_position)
WHERE "surveys"."id" IN ('1c003de0-a6b0-42e5-9679-938af99e75a2')
) SELECT questions.*, questions_order.sort_position FROM "questions" JOIN
questions_order ON questions.id = questions_order.question_id WHERE
"questions"."survey_id" = '1c003de0-a6b0-42e5-9679-938af99e75a2' ORDER BY
survey_id, questions_order.sort_position
-- Data structure is
survey: {
custom_fields: {
questions_order: [uuid_1, uuid_2, etc]
},
id
}
custom fields is a jsonb field
question: {
id
}
After upgrade, query yields:
ERROR: set-returning functions must appear at top level of FROM
LINE 2: FROM surveys, uuid(jsonb_array_elements_tex...
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