Array Index

From PostgreSQL wiki

Revision as of 19:51, 19 November 2009 by Artacus (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, 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)
Personal tools