Thursday, September 30, 2010

PostgreSQL 9.1 - iteration over array

I finished patch that enable iteration over array. I hope so new syntax can helps to better readability. There are a few samples from regress tests:
create or replace function fora_test()
returns int as $$
declare x int; s int = 0;
begin
  for x in array array[1,2,3,4,5,6,7,8,9,10]
  loop
    s := s + x;
  end loop;
  return s;
end;
$$ language plpgsql;

select fora_test();

create or replace function subscripts(anyarray, int)
returns int[] as $$
select array(select generate_subscripts($1,$2));
$$ language sql;

create or replace function fora_test()
returns int as $$
declare x int; s int = 0;
   a int[] := array[1,2,3,4,5,6,7,8,9,10];
begin
  for x in array subscripts(a, 1)
  loop
    s := s + a[x];
  end loop;
  return s;
end;
$$ language plpgsql;

create or replace function fora_test()
returns int as $$
declare x record;
   a fora_point[] := array[(1,2),(3,4),(5,6)];
begin
  for x in array a
  loop
    raise notice 'point=%', x.x;
  end loop;
  return 0;
end;
$$ language plpgsql;

select fora_test();

create or replace function fora_test()
returns int as $$
declare x int; y int;
   a fora_point[] := array[(1,2),(3,4),(5,6)];
begin
  for x, y in array a
  loop
    raise notice 'point=%,%', x, y;
  end loop;
  return 0;
end;
$$ language plpgsql;