Array Index

From PostgreSQL wiki
Jump to navigationJump to search

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)