SQL 调用返回refcursor的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6674787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling a function that returns a refcursor
提问by Ken Chan
I am using Postgresql 8.3 and have the following simple function that will return a refcursorto the client
我正在使用 Postgresql 8.3 并具有以下简单的函数，它将refcursor向客户端返回 a
CREATE OR REPLACE FUNCTION function_1() RETURNS refcursor AS $$
DECLARE
ref_cursor REFCURSOR;
BEGIN
OPEN ref_cursor FOR SELECT * FROM some_table;
RETURN (ref_cursor);
END;
$$ LANGUAGE plpgsql;
Now , I can use the following SQL commands to call this function and manipulate the returned cursor ,but the cursor name is automatically generated by the PostgreSQL
现在，我可以使用以下 SQL 命令来调用该函数并操作返回的游标，但是游标名称是由 PostgreSQL 自动生成的
BEGIN;
SELECT function_1(); --It will output the generated cursor name , for example , "<unnamed portal 11>" ;
FETCH 4 from "<unnamed portal 11>";
COMMIT;
Besides , explicitly declaring the cursor name as the input parameter of the function as described by 38.7.3.5. Returning Cursors.Can I declare my own cursor name and use this cursor name to manipulate the returned cursor instead of Postgresql automatically generates for me ? If not , are there any commands that can get the generated cursor name ?
此外，如38.7.3.5所述，显式声明游标名称作为函数的输入参数 。返回游标。我可以声明我自己的游标名称并使用这个游标名称来操作返回的游标而不是 Postgresql 自动为我生成的吗？如果没有，是否有任何命令可以获得生成的游标名称？
采纳答案by Grzegorz Szpetkowski
Yes, use:
是的，使用：
CREATE OR REPLACE FUNCTION function_1(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN FOR SELECT * FROM some_table;
RETURN ;
END;
$$ LANGUAGE plpgsql;
Result:
结果：
SELECT function_1('myowncursorname');
function_1
-----------------
myowncursorname
(1 row)
It looks like auto-generated name is <unnamed portal n>, where nis natural number (from 1).
看起来自动生成的名称是<unnamed portal n>，其中n是自然数（从 1 开始）。
EDIT:
编辑：
As another way you could use pg_cursorsview with such query to obtain generated cursor name:
作为另一种方式，您可以使用pg_cursors带有此类查询的视图来获取生成的游标名称：
SELECT name FROM pg_cursors WHERE statement LIKE 'SELECT * FROM some_table';
For example:
例如：
BEGIN;
SELECT function_1();
SELECT name FROM pg_cursors WHERE statement LIKE 'SELECT * FROM some_table';
COMMIT;
Result:
结果：
function_1
--------------------
<unnamed portal 3>
(1 row)
name
--------------------
<unnamed portal 3>
(1 row)
回答by VoidMain
I'm not quite sure from wich version of Postgre this is available (in 8.4 it is valid) but i found quite easiest to define the cursor name when you declare it, like this:
我不太确定从哪个版本的 Postgre 可用（在 8.4 中它是有效的），但我发现在声明时定义游标名称非常容易，如下所示：
CREATE OR REPLACE FUNCTION function_1() RETURNS refcursor AS $$
DECLARE
ref_cursor REFCURSOR := 'mycursor';
BEGIN
OPEN ref_cursor FOR SELECT * FROM some_table;
RETURN (ref_cursor);
END;
$$ LANGUAGE plpgsql;
And then you can get it like this:
然后你可以像这样得到它：
BEGIN;
SELECT function_1();
FETCH 4 from mycursor;
COMMIT;
I find this method less cumbersome. Hope that helps.
我觉得这个方法不那么麻烦。希望有帮助。
