调用返回refcursor的函数 [英] Calling a function that returns a refcursor

问题描述

我使用Postgresql 8.3，并有以下简单的函数，将 refcursor 返回给客户端

  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; 
  

现在，我可以使用以下SQL命令来调用此函数并处理返回的游标，游标名称由PostgreSQL自动生成

  BEGIN; 
 SELECT function_1（）; - 它将输出所生成的游标名称，例如<未命名的门户11> ; 
 FETCH 4 from< unnamed portal 11>; 
 COMMIT;除此之外，显式声明游标名称作为函数的输入参数，如  

< http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html =noreferrer> 38.7.3.5。返回游标。我可以声明自己的游标名称，并使用此游标名称来操作返回的游标，而不是Postgresql自动为我生成的游标名称？如果没有，是否有任何命令可以获得生成的游标名称？

解决方案

是，使用：

  CREATE OR REPLACE FUNCTION function_1（refcursor）RETURNS refcursor AS $$ 
 BEGIN 
 OPEN $ 1 FOR SELECT * FROM some_table; 
 RETURN $ 1; 
 END; 
 $$ LANGUAGE plpgsql; 
  

结果：

 code> SELECT function_1（'myowncursorname'）; 
 function_1 
 ----------------- 
 myowncursorname 
（1 row）
  

看起来自动生成的名称是< unnamed portal n> ，其中 n 是自然数（从1开始）。

EDIT： b

另一种方法是使用 pg_cursors 查看此类查询以获取生成的游标名称：

  SELECT name FROM pg_cursors WHERE语句LIKE'SELECT * FROM some_table'; 
  

例如：

  BEGIN; 
 SELECT function_1（）; 
 SELECT name FROM pg_cursors WHERE语句LIKE'SELECT * FROM some_table'; 
 COMMIT; 
  

结果：

 code> function_1 
 -------------------- 
< unnamed portal 3> 
（1 row）
 
 name 
 -------------------- 
< unnamed portal 3> ; 
（1 row）
  

I am using Postgresql 8.3 and have the following simple function that will return a refcursor to the client

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

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 ?

解决方案

Yes, use:

CREATE OR REPLACE FUNCTION function_1(refcursor) RETURNS refcursor AS $$
BEGIN
        OPEN $1 FOR SELECT * FROM some_table;
        RETURN $1;    
END;
$$ LANGUAGE plpgsql;

Result:

SELECT function_1('myowncursorname');
   function_1
-----------------
 myowncursorname
(1 row)

It looks like auto-generated name is <unnamed portal n>, where n is natural number (from 1).

EDIT:

As another way you could use pg_cursors view with such query to obtain generated cursor name:

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)

这篇关于调用返回refcursor的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！