Re: obtaining ARRAY position for a given match

Sam Mason wrote:
> 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.

I agree that it should return null when the item is not found. So I
tested both and Sam is correct. His function performs the same whether
there are 500 elements or 50,000.

We had an idx() function in the _int contrib module. I wonder if it
would be useful to write this in C now that _int is deprecated?

Scott