2010/9/28 Itagaki Takahiro <itagaki.takahiro@gmail.com>:
> On Tue, Sep 28, 2010 at 3:24 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> I looked on some constructs that helps with iteration over array in
>> plpgsql. I propose a following syntax:
>>
>> FOR var IN [array variable | array expression]
>
> What is the benefits compared with
> FOR ... IN SELECT unnest(array) or generate_subscripts(array) ?
>
the speed
SELECT unnest() is full query, but array_expression is just simple
query and can be evaluated by
exec_eval_simple_expr - it can be significantly times faster.
CREATE OR REPLACE FUNCTION f1()
RETURNS void AS $$
DECLARE a int[] := ARRAY[1,2,3,4];
s int;
BEGIN FOR i IN 1..10000 LOOP s := 0; FOR j IN array_lower(a,1)..array_upper(a,1) LOOP s := s + a[j]; END
LOOP;END LOOP;
END;
$$ LANGUAGE plpgsql;
take about 255ms
CREATE OR REPLACE FUNCTION f1()
RETURNS void AS $$
DECLARE a int[] := ARRAY[1,2,3,4]; j int;
s int;
BEGIN FOR i IN 1..10000 LOOP s := 0; FOR j IN SELECT unnest(a) LOOP s := s + j; END LOOP; END LOOP;
END;
$$ LANGUAGE plpgsql;
it takes abou 1000ms
Regards
Pavel Stehule
> --
> Itagaki Takahiro
>