is there a way to preserve order or array when using ANY in postgres query?

user_600515 0
Viewed 276

I'd like to be able to do a query using ANY that maintains the order of the array passed into the any function. Consider this simple example:

create table stuff (
    id serial,
    value int
);
insert into stuff (value) values (1), (2), (3), (4), (5);

select * from stuff where value = ANY(ARRAY[1,2,3,4,5]);
select * from stuff where value = ANY(ARRAY[5,4,3,2,1]);

which results in the same order for both queries, even though the arrays had a different order.

----+-------
  1 |     1
  2 |     2
  3 |     3
  4 |     4
  5 |     5
(5 rows)

 id | value 
----+-------
  1 |     1
  2 |     2
  3 |     3
  4 |     4
  5 |     5
(5 rows)

I'd like to have a shorthand way, if possible, to preserve results in order of array inside of the ANY. Is this possible?

So far I've had to write something like this, which feels a bit heavy-handed:


CREATE FUNCTION ordered_any (
 ints int[]
) RETURNS int[] as $$
DECLARE
   results int[];
   i int;
   value int;
BEGIN
  FOR i IN 1 .. cardinality(ints) LOOP
    SELECT f.id FROM stuff f 
      WHERE f.value = ints[i]
    INTO value;
    results = array_append(results,  value);
  END LOOP;

  RETURN results;
END;
$$
LANGUAGE 'plpgsql';

select ordered_any(ARRAY[5,4,3,2,1]);

"Any" help is appreciated! No pun intended ;)

postgresql arrays sql
1 Answers
select 
   id,
   value, 
   array_position(array[5,4,3,2,1],id) as ord 
from stuff where value=any(array[5,4,3,2,1]) 
order by ord;

output:

 id | value | ord
----+-------+-----
  5 |     5 |   1
  4 |     4 |   2
  3 |     3 |   3
  2 |     2 |   4
  1 |     1 |   5
user_7240390
Related