我按照本教程创建函数http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure
这是代码
CREATE FUNCTION func1(vtoken character varying, ref1 refcursor, ref2 refcursor)
RETURNS SETOF refcursor AS $BODY$
DECLARE
rec record;
BEGIN
OPEN ref1 FOR
SELECT * FROM table1;
RETURN NEXT ref1;
OPEN ref2 FOR
SELECT * FROM table2;
RETURN NEXT ref2;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;
我想创建另一个函数并调用func1 .
CREATE OR REPLACE FUNCTION script(vcodebar character varying)
RETURNS void AS
$BODY$
DECLARE
BEGIN
SELECT func1(vtoken,'details', 'amount');
FETCH ALL IN "details";
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
但是我收到一个错误:
FETCH ALL IN“细节”;
我无法在调用func1之前添加BEGIN ... COMMIT,因为它包含在块BEGIN ... END中;
如何使用func1中的2个refcursors?
1 回答
一旦对函数的调用完成,函数返回的游标就会关闭 . 您应该从
SELECT
语句中包装块,直到事务块中最后一次使用游标为止: