Re: Converting each item in array to a query result row

Thanks for all the replies. I'm going to post the results of using
the recommended approach in another thread.

On Fri, May 29, 2009 at 1:18 PM, Adam Ruth <adamruth(at)mac(dot)com> wrote:
> Good point, I should have specified 8.3.7.
>
> Just one more reason to anxiously anticipate upgrading to 8.4.
>
>
>
> On 30/05/2009, at 2:56 AM, Tom Lane wrote:
>
>> Adam Ruth <adamruth(at)mac(dot)com> writes:
>>>
>>> Always test your performance assumptions. The plpgsql function is
>>> faster than the sql function, a lot faster on smaller arrays.
>>
>> And, of course, it also pays to be precise about what you're testing
>> and on what.  Set-returning SQL functions got a lot faster in 8.4.
>> Using CVS HEAD on a not-very-fast machine, I get these timings for
>> the attached script (10000 loop iterations in all cases)
>>
>>                        10 elements     100 elements    1000 elements
>>
>> built-in unnest         2.44            6.52            47.96
>> SQL function            2.52            6.50            46.71
>> plpgsql function        3.63            12.47           101.68
>>
>> So at least in this specific test condition, there's not much
>> perceptible difference between the SQL function and the builtin,
>> while plpgsql lags behind.
>>
>>                        regards, tom lane
>>
>>
>> create or replace function testit(n int, l int) returns float8 as $$
>> declare arr int[];
>>  st timestamptz;
>>  et timestamptz;
>> begin
>>  arr := '{}';
>>  for i in 1 .. n loop
>>   arr[i] = i;
>>  end loop;
>>  st := clock_timestamp();
>>  for i in 1 .. l loop
>>   perform count(*) from unnest(arr); -- or unnest_sql or unnest_plpgsql
>>  end loop;
>>  et := clock_timestamp();
>>  return extract(epoch from et - st);
>> end $$ language plpgsql;
>>
>> CREATE or replace FUNCTION unnest_sql(anyarray) RETURNS SETOF anyelement
>> AS
>> $_$
>> SELECT ($1)[i] FROM generate_series(array_lower($1,1),array_upper($1,1))
>> i;
>> $_$
>>   LANGUAGE sql IMMUTABLE;
>>
>> create or replace function unnest_plpgsql(_a anyarray) returns setof
>> anyelement as $$
>> begin
>>        for i in array_lower(_a,1) .. array_upper(_a,1) loop
>>                return next _a[i];
>>        end loop;
>>        return;
>> end;
>> $$ language plpgsql strict immutable;
>
>