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