Re: obtaining ARRAY position for a given match - Mailing list pgsql-general

Pedro Doria Meunier wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> I'm trying to get the array position for a given match as thus:
>
> This gets me the record for a given match:
> SELECT *  FROM garmin_units WHERE 'L' = ANY (protocol_tag);
>
> Ok. so far so good...
> But what about getting the array position at which 'L' is stored?
>
> Searching the Postgresql docs gives me no answer... :(
> Am I on a wild goose chase?
>
> Any insight highly appreciated ;)
>
> BR,
> Pedro Doria Meunier.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAksFP6IACgkQ2FH5GXCfxAuasgCgu/d68fkg16r1OF/2QSLnmwhW
> gjYAniyQ1Mn/72323NSznxgakF4dn98k
> =tWbI
> -----END PGP SIGNATURE-----

I wrote this a while ago. If you are on 8.4 use unnest instead. And if
you are searching thru really big arrays, use plpgsql so you can
terminate when you find a match.

CREATE OR REPLACE FUNCTION idx(text[], text)
RETURNS int AS
$$
SELECT MIN(CASE WHEN $1[i] = $2 THEN i
ELSE NULL END)::int
FROM generate_series(array_lower($1, 1),
array_upper($1, 1)) i;
$$
LANGUAGE 'sql' IMMUTABLE STRICT;