Skip to content

Instantly share code, notes, and snippets.

@niwinz
Created October 21, 2012 09:24
Show Gist options
  • Save niwinz/3926482 to your computer and use it in GitHub Desktop.
Save niwinz/3926482 to your computer and use it in GitHub Desktop.
Array order with postgres
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;
test=# create table foo (bar int);
CREATE TABLE
test=# insert into foo (bar) values (1), (2), (3), (4);
INSERT 0 4
test=# select * from foo;
bar
-----
1
2
3
4
(4 rows)
test=# select * from foo where foo.bar in (4,1,3,2);
bar
-----
1
2
3
4
(4 rows)
test=# select * from foo order by idx(array[4,1,3,2], bar);
bar
-----
4
1
3
2
(4 rows)
test=# select * from foo where bar in (4,1) order by idx(array[4,1,3,2], bar);
bar
-----
4
1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment