Re: [GENERAL] Ordering by IN

[Eric B . Ridge]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster