Re: Return rows in input array's order?

On Tue, May 9, 2023 at 11:23 AM David Wheeler <hippysoyboy(at)gmail(dot)com> 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...