Created
January 5, 2018 16:57
-
-
Save nathansgreen/f9b527de5d5691de0249c9c4dc61ec10 to your computer and use it in GitHub Desktop.
PostgreSQL <9.5 array_position function
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- The array_position function was added in Postgres 9.5. | |
-- For older versions, you can get the same behavior with this function. | |
create function array_position(arr ANYARRAY, elem ANYELEMENT, pos INTEGER default 1) returns INTEGER | |
language sql | |
as $BODY$ | |
select row_number::INTEGER | |
from ( | |
select unnest, row_number() over () | |
from ( select unnest(arr) ) t0 | |
) t1 | |
where row_number >= greatest(1, pos) | |
and (case when elem is null then unnest is null else unnest = elem end) | |
limit 1; | |
$BODY$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Great! Thank you!!!