Waiting for 9.1 – FOREACH IN ARRAY

On 16th of February, Tom Lane committed patch:

Add FOREACH IN ARRAY looping to plpgsql.
 
(I'm not entirely sure that we've finished bikeshedding the syntax details,
but the functionality seems OK.)
 
Pavel Stehule, reviewed by Stephen Frost and Tom Lane

This adds simpler syntax to capability that was already there, but it's easier to write and easier to read, so I think it's a good thing.

When you had previously array variable in PL/pgSQL, you had to either use generate_subscripts, or unnest:

CREATE OR REPLACE FUNCTION test( in_array TEXT[] ) RETURNS void AS $$
DECLARE
    i INT4;
    t TEXT;
BEGIN
    FOR i IN SELECT generate_subscripts( in_array, 1 ) LOOP
        raise notice 'i: %', in_array[ i ];
    END loop;
    FOR t IN SELECT unnest( in_array ) LOOP
        raise notice 't: %', t;
    END loop;
END;
$$ LANGUAGE plpgsql;
 
SELECT test( array['a', 'b', 'c'] );
psql:z.sql:15: NOTICE:  i: a
psql:z.sql:15: NOTICE:  i: b
psql:z.sql:15: NOTICE:  i: c
psql:z.sql:15: NOTICE:  t: a
psql:z.sql:15: NOTICE:  t: b
psql:z.sql:15: NOTICE:  t: c
 test
------
 
(1 ROW)

Now, you can use simpler syntax:

CREATE OR REPLACE FUNCTION test( in_array TEXT[] ) RETURNS void AS $$
DECLARE
    t TEXT;
BEGIN
    FOREACH t IN ARRAY in_array LOOP
        raise notice 't: %', t;
    END loop;
END;
$$ LANGUAGE plpgsql;
 
SELECT test( array['a', 'b', 'c'] );
psql:z.sql:11: NOTICE:  t: a
psql:z.sql:11: NOTICE:  t: b
psql:z.sql:11: NOTICE:  t: c
 test
------
 
(1 ROW)

What is interesting, that when passing two dimensional array, it works like this:

SELECT test( '{{a,b,c},{d,e,f},{g,h,i}}' );
psql:z.sql:11: NOTICE:  t: a
psql:z.sql:11: NOTICE:  t: b
psql:z.sql:11: NOTICE:  t: c
psql:z.sql:11: NOTICE:  t: d
psql:z.sql:11: NOTICE:  t: e
psql:z.sql:11: NOTICE:  t: f
psql:z.sql:11: NOTICE:  t: g
psql:z.sql:11: NOTICE:  t: h
psql:z.sql:11: NOTICE:  t: i

If you'd prefer to iterate over sub arrays, it's simple thanks to “SLICE" syntax:

CREATE OR REPLACE FUNCTION test( in_array TEXT[] ) RETURNS void AS $$
DECLARE
    t TEXT[];
BEGIN
    FOREACH t SLICE 1 IN ARRAY in_array LOOP
        raise notice 't: %', t;
    END loop;
END;
$$ LANGUAGE plpgsql;
 
SELECT test( '{{a,b,c},{d,e,f},{g,h,i}}' );
psql:z.sql:11: NOTICE:  t: {a,b,c}
psql:z.sql:11: NOTICE:  t: {d,e,f}
psql:z.sql:11: NOTICE:  t: {g,h,i}

All in all, it looks like a cool addition.

4 thoughts on “Waiting for 9.1 – FOREACH IN ARRAY”

  1. Hello

    The parentheses around a variable are not necessary.

    Main goal of this functionality is effective iteration over large arrays

    Pavel

  2. @Pavel:
    Thanks for info – removed them.

    Ah, so, as I understand it will be better/faster/less-memory-consuming than previous approaches? Nice.

  3. The performance depends on usage – it should be always faster then FOR iteration with subscripts or via unnest:

    * one detoast
    * continuous seq scan of detoasted data
    * it uses a simply expression

    And it remove some pathological performance issue – so it can 30-40x and more faster than classic FOR

Comments are closed.