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 1pwUcx-0005R1-VR for pgsql-general@arkaria.postgresql.org; Tue, 09 May 2023 21:12:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pwUcw-00069P-Bk for pgsql-general@arkaria.postgresql.org; Tue, 09 May 2023 21:12:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pwUcw-00069F-1g for pgsql-general@lists.postgresql.org; Tue, 09 May 2023 21:12:06 +0000 Received: from mta1.recol.net ([64.207.103.6]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pwUcs-001e8i-LJ for pgsql-general@postgresql.org; Tue, 09 May 2023 21:12:05 +0000 Received: from localhost (static-72-65-219-98.eriepa.fios.verizon.net [72.65.219.98]) by mta1.recol.net (Postfix) with ESMTPSA id C7C50398D87; Tue, 9 May 2023 17:11:59 -0400 (EDT) Date: Tue, 9 May 2023 17:11:04 -0400 From: Steven Lembark To: Dominique Devienne Cc: David Wheeler , pgsql-general@postgresql.org, lembark@wrkhors.com Subject: Re: Return rows in input array's order? Message-ID: <20230509171104.583e6435.lembark@wrkhors.com> In-Reply-To: References: <5C5270F1-4323-458F-99C2-1D187039EA9C@gmail.com> Organization: Workhorse Computing X-Mailer: Claws Mail 4.1.0 (GTK 3.24.34; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 9 May 2023 11:37:29 +0200 Dominique Devienne wrote: > On Tue, May 9, 2023 at 11:23=E2=80=AFAM David Wheeler > wrote: >=20 > > > Hi. With an integer identity primary key table, > > > we fetch a number of rows with WHERE id =3D ANY($1), > > > with $1 an int[] array. The API using that query must return > > > rows in the input int[] array order, and uses a client-side > > > mapping to achieve that currently. > > > > > > Is it possible to maintain $1's order directly in SQL? > > > Efficiently? =20 > > > > We=E2=80=99ve done this before with an =E2=80=9Corder by array_index(id, > > input_array)=E2=80=9D. I forget the actual function consider that pseudo > > code=20 >=20 > Thanks David. I see how this would work. >=20 > It was only used for small arrays but never noticed any performance > issues >=20 Depending on your PG version: Create a temp table via unnest, join that with what you need=20 and order by tmp.seq. Forgot which version allows inlining of CTE's but you can=20 use a CTE (12?): with int_seq as ( select unnest( int_array_col ) "order_by" from whatever where blah ) select from foobar a join=20 int_seq b on=20 a.foo =3D b.order_by order by b.order_by , =20 This dodges the tmp table and the optimizer can inline the=20 results, probably gets you the fastest result.=20 --=20 Steven Lembark Workhorse Computing lembark@wrkhors.com +1 888 359 3508