Postgresql's FIND_IN_SET

FIND_IN_SET is a nifty Mysql function for finding the value's index from a given set, where the given set is a dynamic set of values.

We could use this function when we want to sort the list based on a given set of filter

Test data:

create table x_n as select x.y from (values(9),(13),(15),(30),(27),(63)) as x(y)

Test query:

select * from x_n 
where y in (15,9,13,27)
order by find_in_set(y, '15,9,13,27') 

Output:

15
9
13
27

Here's the find_in_set function for Postgres:

create function find_in_set(n int, s text) returns bigint as
$$
select z.row_number
from
(
    select row_number() over(), y.x 
    from (select unnest(('{' || $2 || '}')::int[]) as x) as y
) as z
where z.x = $1
$$ language sql;

Here's the function for pre-8.4:

create function find_in_set(n int, s text) returns bigint as
$$
declare a int[];
begin

    a = ('{' || $2 || '}')::int[];

    return (
        select i 
        from 
        generate_series(1, array_upper(a, 1)) as gs(i)
        where a[i] = $1 );

end;
$$ language 'plpgsql';