Cursors in SQL/PSM samples

cycles

-- basic cycle
CREATE OR REPLACE FUNCTION fx(a int)
RETURNS int AS
$$
  BEGIN
      DECLARE done bool DEFAULT false;
      DECLARE aux int;

      DECLARE cx CURSOR FOR
          SELECT Foo.a FROM Foo;

      DECLARE CONTINUE HANDLER FOR NOT FOUND
          SET done = true;

      OPEN cx;

      FETCH cx INTO aux;
   sl:WHILE NOT done DO
          SET a = a + aux;
          PRINT fx.a, aux;
          FETCH cx INTO aux;
      END WHILE sl;

      CLOSE cx;
      RETURN a;
  END;
$$ LANGUAGE plpgpsm;

-- cycle controled by SQLSTATE variable
CREATE OR REPLACE FUNCTION fx(a INT)
RETURNS int AS
$$
  BEGIN
    DECLARE aux int;
    DECLARE SQLSTATE char(5);

    DECLARE cx CURSOR FOR
       SELECT * FROM Foo;

    OPEN cx;

    FETCH cx INTO aux;
    WHILE SQLSTATE = '00000' DO
      SET a = a + aux;
      PRINT fx.a, aux;
      FETCH cx INTO aux;
    END WHILE;

    CLOSE cx;
    RETURN a;
  END;
$$ LANGUAGE plpgpsm;

-- crazy sample of for stmt
CREATE OR REPLACE FUNCTION fx(i integer) 
RETURNS void AS
$$
  BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND PRINT 'not found';

 k: FOR aaa AS bbb CURSOR FOR 
        SELECT * 
           FROM generate_series(1, i) as g(idx)
          WHERE i > 10000
    DO
      IF idx > 10 THEN LEAVE k; END IF;
      PRINT idx;
    END FOR k;

    PRINT 'done';
  END;
$$ LANGUAGE plpgpsm;

Scrollable cursor support

CREATE OR REPLACE FUNCTION fx() 
RETURNS void AS
$$
  BEGIN
    DECLARE x, y integer;
    DECLARE c SCROLL CURSOR FOR 
            SELECT * FROM Foo;
    OPEN c;
    FETCH ABSOLUTE -1 FROM c INTO x, y;
    PRINT x, y;
    FETCH RELATIVE -1 FROM c INTO x, y;
    PRINT x, y;
    FETCH FROM c INTO x, y;
    PRINT x, y;
    CLOSE c;
  END;
$$ LANGUAGE plpgpsm;