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