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

2010/9/28 Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>:
> On Tue, Sep 28, 2010 at 3:24 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>