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)