从游标 PostgreSQL 中获取行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28005863/
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
Fetching rows from a cursor PostgreSQL
提问by Mike Pérez
I have the cursor code:
我有光标代码：
BEGIN;
DECLARE cliente_cursor
CURSOR FOR SELECT * FROM cliente;
I want to read all the content from the Table 'cliente':
我想从“客户”表中读取所有内容：
With the use of a cursor. I have the code working for SQL Server:
使用游标。我有适用于 SQL Server 的代码：
DECLARE cliente_cursor CURSOR
FOR SELECT * FROM cliente
OPEN cliente_cursor
FETCH NEXT FROM cliente_cursor;
While @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM cliente_cursor;
End
CLOSE cliente_cursor
DEALLOCATE cliente_cursor
And I want to have a working code for PostgreSQL.
我想有一个 PostgreSQL 的工作代码。
I have been looking for a solution & seen people usually suggest using functions. I wonder if there is any way in this PostgreSQL DBMS to create something similar to the code in SQL Server.
我一直在寻找解决方案，看到人们通常建议使用函数。我想知道在这个 PostgreSQL DBMS 中是否有任何方法可以创建类似于 SQL Server 中的代码的东西。
I had written this code:
我写过这段代码：
CREATE OR REPLACE FUNCTION MyFunction()
RETURNS setof cliente AS $$
DECLARE
cursor_cliente CURSOR FOR SELECT * FROM cliente;
rec cliente%ROWTYPE;
BEGIN
OPEN cursor_cliente;
loop
--fetch the table row inside the loop
FETCH cursor_cliente INTO rec;
-- check if there is no record
--exit from loop when record not found
if not found then
exit ;
end if;
end loop;
RETURN;
END;
$$ LANGUAGE plpgsql;
But when I run it, I only get:
但是当我运行它时，我只会得到：
select MyFunction();
Any idea what should the code be instead?
知道代码应该是什么吗？
Any help would be appreciated a lot!
任何帮助将不胜感激！
回答by Jasen
CREATE OR REPLACE FUNCTION foo() RETURNS setof cliente
language plpgsql AS $$
DECLARE
x cliente%rowtype ;
BEGIN
FOR x IN SELECT * FROM cliente loop
RETURN NEXT x;
END loop;
END $$;
SELECT * FROM foo();
it can also be done with an explicit cursor.
也可以使用显式游标来完成。
CREATE OR REPLACE FUNCTION foo() RETURNS setof cliente
language plpgsql as $$
DECLARE
x cliente%rowtype ;
cliente_cursor CURSOR FOR SELECT * FROM cliente;
BEGIN
FOR x IN cliente_cursor loop
RETURN NEXT x;
END loop;
END $$;
SELECT * FROM foo();
The function is going to persist so either give it a useful name and keep it around or drop it after you are done.
该函数将持续存在，因此要么给它一个有用的名称并保留它，要么在完成后删除它。
If you want a private name for the function pg_temp.foowill be private to your session.
如果您希望该函数的私有名称对 pg_temp.foo您的会话是私有的。
回答by Evgeny Nozdrev
If you want just to return all rows from the query, use
如果您只想从查询中返回所有行，请使用
RETURN QUERY
SELECT ...
and RETURNS TABLE(column1 type1, column2 type2, ...)as function's type.
并RETURNS TABLE(column1 type1, column2 type2, ...)作为函数的类型。
Or for cursor:
或者对于光标：
RETURN QUERY
FETCH ALL FROM cliente_cursor;
To do something with each row, use
要对每一行做一些事情，请使用
FOR _record IN
SELECT ...
LOOP
<action1>;
<action2>;
...
END LOOP;
from this answer.
从这个答案。
Or for cursor:
或者对于光标：
FOR _record IN
FETCH ALL FROM ...
LOOP
<action1>;
<action2>;
...
END LOOP;
for cursor.
为光标。
Note that PostgreSQL has refcursortype which allows you to use a cursor by it text name. IMHO it is simplest way (read more)
请注意，PostgreSQL 具有refcursor允许您通过文本名称使用游标的类型。恕我直言，这是最简单的方法（阅读更多）
