Array Index

Snippets

Array Index

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

Return the index of the first occurrence of a value in an array. Kodos to Sam Mason for this one.

CREATE OR REPLACE FUNCTION idx(anyarray, anyelement) RETURNS int AS $$ SELECT i FROM ( SELECT generate_series(array_lower($1,1),array_upper($1,1)) ) g(i) WHERE $1[i] = $2 LIMIT 1; $$ LANGUAGE sql IMMUTABLE; 

Can also be useful for sorting.

SELECT * FROM foo ORDER BY idx(array['Freshman','Sophomore','Junior','Senior'], foo.grade_level)