85.160.67.167: /* cycles */

2007-01-04T05:41:15Z

cycles

Nová stránka

=== cycles ===
<pre>
-- 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;
</pre>

=== Scrollable cursor support ===
<pre>
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;
</pre>

Cursors in SQL/PSM samples - Historie editací

85.160.67.167: /* cycles */