Re: obtaining ARRAY position for a given match

On Thu, Nov 19, 2009 at 05:24:33PM +0100, Pavel Stehule wrote:
> it should be little bit more effective:

I'm not sure if it will be much more; when you put a set returning
function into a FROM clause PG will always run the function to
completion---as far as I know, but I've only got 8.3 for testing at the
moment. I'm also not sure why you want to return zero when you don't
find the element. The code also exploits an implementation artifact of
PG that the zero (i.e. the RHS of your UNION ALL) will be "after" the
real index.

This raises a small and interesting optimization for PG, when it does
the plan it could notice that a UNION ALL followed by a LIMIT won't need
to return all rows and hence it may be better to run the "quicker" one
first. Or would this end up breaking more code than it helps?

> 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)

Quality typo :) ^^^

> WHERE $1[i] = $2
> UNION ALL
> SELECT 0 -- return 0 as not found
> LIMIT 1; -- stop after first match
> $$ LANGUAGE sql;

I'd do something like:

CREATE OR REPLACE FUNCTION firstidx(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;

You can replace the call to array_upper with some large number to check
either function's behavior with large arrays.

--
Sam http://samason.me.uk/