Re: Proposal: plpgsql - "for in array" statement

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
>