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';

Awesome, thanks a lot for this. I am using it in my project and it was just what I needed :)

ReplyDelete