Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lK6jv-0005PE-W8 for pgsql-bugs@arkaria.postgresql.org; Wed, 10 Mar 2021 21:51:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lK6ju-0001lZ-Rz for pgsql-bugs@arkaria.postgresql.org; Wed, 10 Mar 2021 21:51:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lK6ju-0001kO-Lx for pgsql-bugs@lists.postgresql.org; Wed, 10 Mar 2021 21:51:34 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lK6js-00074k-Mk for pgsql-bugs@lists.postgresql.org; Wed, 10 Mar 2021 21:51:33 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 12ALpVjK2218081; Wed, 10 Mar 2021 16:51:31 -0500 From: Tom Lane To: Jason Ayre cc: pgsql-bugs@lists.postgresql.org Subject: Re: 12.4 -> 12.5 upgrade, broken CTE query In-reply-to: References: Comments: In-reply-to Jason Ayre message dated "Wed, 10 Mar 2021 14:04:21 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2218079.1615413091.1@sss.pgh.pa.us> Date: Wed, 10 Mar 2021 16:51:31 -0500 Message-ID: <2218080.1615413091@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Jason Ayre 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