(rozdíl) ← Starší verze | zobrazit aktuální verzi (rozdíl) | Novější verze → (rozdíl)
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;