Search Postgresql Archives

Re: Return rows in input array's order?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Dominique:

Take a look to the "unnest()" function. It transforms an array into a set of rows. I believe I used it in the past to do something similar to what you need.

Another option is to use a "values" _expression_ (in a subquery) instead of an array, and build the query dynamically.

Best regards.


On 09/05/2023 11:37, Dominique Devienne wrote:
On Tue, May 9, 2023 at 11:23 AM David Wheeler <hippysoyboy@xxxxxxxxx> wrote:
> Hi. With an integer identity primary key table,
> we fetch a number of rows with WHERE id = 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?

We’ve done this before with an “order by array_index(id, input_array)”. I forget the actual function consider that pseudo code

Thanks David. I see how this would work. 

It was only used for small arrays but never noticed any performance issues

Hmmm, sounds like this would be quadratic though...

Each call to array_index() will be O(N), so turn the sort into O(N^2) just from the array_index() calls,
without even considering the sorting itself (which I assume is O(N log N)).

I wonder whether the int[] can be turned into a pseudo table with a ROWNUM extra generated column that
would then be (LEFT) JOIN'd to the accessed table, so that the original array index is readily accessible.
Would something like this be possible in Postgres' SQL?

I could then skip the sort, return that original index as part of the select,
and thus be able to read the other columns directly in the correct client-side re-allocated vector-slot / structure...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux