Re: Ordering by IN

On Aug 25, 2004, at 2:18 AM, Hadley Willan wrote:

>  Hi,
>      I was wondering if it's possible to order the result set by some
> of the set contained in an IN clause.
>

I had to do something like this recently.  Ended up with a pl/pgsql
function, looked a lot like this:

create or replace function array_find (int8, int8[]) returns int4 as
     'declare
         data alias for $1;
         arr alias for $2;
         cnt int4;
      begin
         cnt := 1;
         while arr[cnt] is not null loop
             if data = arr[cnt] then
                 return cnt;
             end if;
             cnt := cnt + 1;
         end loop;

         return null;
      end;'
language 'plpgsql';

Then, using your original query:

SELECT * FROM v_fol_unit_pub_utmpt
    WHERE folder_folder_object = 100120 AND
        unit IN ( 90072, 90005, 90074, 90075 ) AND
        unit_pub_type IN ( 2 ) AND
        utmpt IN ( 1 )
    ORDER BY array_find(unit, '{90072, 90005, 90074, 90075}');

Notice that the values are repeated in the ORDER BY clause, in the form
of an array.  VeryImportant.  Also note that I'm assuming the datatype
of "unit" is an int8... you'll want to adjust the arguments of the
function appropriately for the actual datatype.

With my luck, somebody will respond with a "but postgres already has an
array_find-like function"... but if it does, I couldn't find it.

eric