pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed12.4 -> 12.5 upgrade, broken CTE query
2+ messages / 2 participants
[nested] [flat]
* 12.4 -> 12.5 upgrade, broken CTE query
@ 2021-03-10 21:04 Jason Ayre <jasonayre@gmail.com>
2021-03-10 21:51 ` Re: 12.4 -> 12.5 upgrade, broken CTE query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Jason Ayre @ 2021-03-10 21:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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...
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: 12.4 -> 12.5 upgrade, broken CTE query
2021-03-10 21:04 12.4 -> 12.5 upgrade, broken CTE query Jason Ayre <jasonayre@gmail.com>
@ 2021-03-10 21:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2021-03-10 21:51 UTC (permalink / raw)
To: Jason Ayre <jasonayre@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-03-10 21:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-10 21:04 12.4 -> 12.5 upgrade, broken CTE query Jason Ayre <jasonayre@gmail.com>
2021-03-10 21:51 ` Tom Lane <tgl@sss.pgh.pa.us>
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