2009/11/19 Scott Bailey <artacus@xxxxxxxxxxx>: > 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; > Hello it should be little bit more effective: CREATE OR REPLACE FUNCTION idx(anyarray, anyelement) RETURNS int AS $$ SELECT i FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i) WHERE $1[i] = $2 UNION ALL SELECT 0 -- return 0 as not found LIMIT 1; -- stop after first match $$ LANGUAGE sql; Regards Pavel Stehule > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
