Skip site navigation (1) Skip section navigation (2)

Proposal: plpgsql - "for in array" statement

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Proposal: plpgsql - "for in array" statement
Date: 2010-09-28 06:24:37
Message-ID: AANLkTikzmmuND9hcBy9vfzRcQTT=a+tEPG9c7izuHOX0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello

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]
LOOP
  ..
END LOOP

var - declared variable - theoretically we can a detect var type from
array type, but it needs a early expression an analyze (not used in
PL/pgSQL), so var should be declared before. This construct ensure
iteration over all items of array. When somebody needs a subscripts
from some dimension, then he can use a proposed function "subscripts".

so iteration over two dimensional array can be written:

DECLARE
  i integer;
  j integer;
BEGIN
  FOR i IN subscripts(myarray, 1) LOOP
    FOR j IN subscripts(myarray, 2) LOOP
      RAISE NOTICE 'myarray[%,%] = %', i, j, myarray[i,j];
    END LOOP;
  END LOOP;

When input array is multidimensional, then this array is flattened -
order of iteration is specified by physical store of items inside the
array. This construct iterate over all items of input array - it has a
same behave as "unnest" function.

some examples:

DECLARE
  v integer;
  a int[] := ARRAY[2,3.4,5];
BEGIN
  FOR val IN a
  LOOP
    RAISE NOTICE '%', val; -- produce 2,3,4,5
  END LOOP;

  FOR val IN subscripts(a, 1)
  LOOP
    RAISE NOTICE '%', val; -- produce 1,2,3,4
  END LOOP;

  FOR val IN subscripts(a,1)
  LOOP
    RAISE NOTICE '%', a[val]; -- produce 2,3,4,5
  END LOOP;
END;

Comments, ideas?

Regards

Pavel Stehule

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-09-28 06:53:48
Subject: Re: Help with User-defined function in PostgreSQL with Visual C++
Previous:From: Li JieDate: 2010-09-28 05:24:37
Subject: Parallel Query Execution Project

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group