postgresql 如何从 PL/pgSQL 函数返回的 refcursor 中选择所有行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11135815/
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
How to select all rows from refcursor returned by PL/pgSQL function?
提问by Danubian Sailor
I have a function some_func() that returns refcursor:
我有一个函数 some_func() 返回refcursor：
CREATE OR REPLACE FUNCTION some_func() RETURNS refcursor AS (...)
I want to call this function from console and display the result set from the cursor returned by it. In Oracle I would write:
我想从控制台调用这个函数并显示它返回的游标的结果集。在 Oracle 中，我会写：
SELECT * FROM TABLE(some_func());
What is the equivalent of that construction on PosgreSQL?
什么是 PosgreSQL 上的构造的等价物？
采纳答案by Daniel Vérité
A refcursor is referred to by its name, either auto-generated or chosen by you. This page of the docgives an example for each.
refcursor 由其名称引用，可以是自动生成的，也可以是您选择的。文档的这个页面为每个页面提供了一个示例。
To fetch results from a refcursor you must have the cursor's name. In the case of generated names that'll be something like <unnamed portal 1>". You can then:
要从 refcursor 获取结果，您必须具有游标的名称。在生成的名称的情况下，将类似于<unnamed portal 1>". 然后你可以：
FETCH ALL FROM "<unnamed portal 1>";
The cursor name is returned from the function as the refcursorresult, so you can get it from there.
游标名称作为refcursor结果从函数返回，因此您可以从那里获取它。
回答by indika
Lets say you have a postgres function written in such a way that return a refcursor;
假设您有一个以返回 refcursor 的方式编写的 postgres 函数；
CREATE OR REPLACE FUNCTION "com.mkindika"."myfunction" ("refcursor", other input parameters) RETURNS "pg_catalog"."refcursor" AS
$body$
DECLARE
---- query
END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
If you want to print the refcursor you may use following lines of code;
如果要打印 refcursor，可以使用以下代码行；
BEGIN;
SELECT "com.mkindika"."myfunction" ("refcursor",other input parameters);
FETCH ALL IN "refcursor";
