PostgreSQL 中基于游标的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22339628/
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
Cursor based records in PostgreSQL
提问by Zeus
I'm trying to use cursors for a query that joins multiple tables. I've seen that for oracle there is a cursor based record. When I try the same for Postgres, it throws some error. How can I do the same in Postgres?
我正在尝试将游标用于连接多个表的查询。我已经看到对于 oracle 有一个基于游标的记录。当我为 Postgres 尝试同样的方法时，它会引发一些错误。我如何在 Postgres 中做同样的事情？
CREATE OR REPLACE FUNCTION avoidable_states()
RETURNS SETOF varchar AS
$BODY$
DECLARE
xyz CURSOR FOR select * from address ad
join city ct on ad.city_id = ct.city_id;
xyz_row RECORD;
BEGIN
open xyz;
LOOP
fetch xyz into xyz_row;
exit when xyz_row = null;
if xyz_row.city like '%hi%' then
return next xyz_row.city;
end if;
END LOOP;
close xyz;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
Error I get is:
我得到的错误是：
ERROR: relation "xyz" does not exist CONTEXT: compilation of PL/pgSQL function "avoidable_states" near line 4
ERROR: relation "xyz" does not exist CONTEXT: compilation of PL/pgSQL function "avoidable_states" near line 4
采纳答案by MatheusOl
Just use the RECORDtype:
只需使用RECORD类型：
DECLARE
...
cur_row RECORD;
BEGIN
...
FETCH xyz INTO cur_row;
EXIT WHEN NOT FOUND;
IF cur_row.city LIKE 'CH%' THEN
...
回答by Erwin Brandstetter
1. Implicit cursor
1.隐式游标
It's almost always better to use the implicit cursor of a FORloopthan to resort to a somewhat slower and unwieldy explicit cursor. I have written thousands of plpgsql functions and only a hand full of times explicit cursors made any sense.
使用FOR循环的隐式游标几乎总是比求助于稍慢和笨拙的显式游标要好。我已经编写了数以千计的 plpgsql 函数，只有一手满倍的显式游标才有意义。
CREATE OR REPLACE FUNCTION avoidable_states()
RETURNS SETOF varchar AS
$func$
DECLARE
rec record;
BEGIN
FOR rec IN
SELECT *
FROM address ad
JOIN city ct USING (city_id)
LOOP
IF rec.city LIKE '%hi%' THEN
RETURN NEXT rec.city;
END IF;
END LOOP;
END
$func$ LANGUAGE plpgsql STABLE;
Aside: there is nothing in the function that would need volatility VOLATILE. Use STABLE.
旁白：函数中没有任何东西需要波动性VOLATILE。使用STABLE.
2. Set-based approach
2. 基于集合的方法
It's almost always better to use a set-based approach if possible. Use RETURN QUERYto return as set from a query directly.
如果可能，使用基于集合的方法几乎总是更好。用于RETURN QUERY直接从查询中返回设置。
CREATE OR REPLACE FUNCTION avoidable_states()
RETURNS SETOF varchar AS
$func$
BEGIN
RETURN QUERY
SELECT ct.city
FROM address ad
JOIN city ct USING (city_id)
WHERE ct.city LIKE '%hi%';
END
$func$ LANGUAGE plpgsql STABLE;
3. SQL function
3.SQL函数
For the simple case (probably a simplification), you might also use a simple SQL functionor even just the query:
对于简单的情况（可能是简化），您还可以使用简单的SQL 函数，甚至只是查询：
CREATE OR REPLACE FUNCTION avoidable_states()
RETURNS SETOF varchar AS
$func$
SELECT ct.city
FROM address ad
JOIN city ct USING (city_id)
WHERE ct.city LIKE '%hi%';
$func$ LANGUAGE sql STABLE;
