PostgreSQL中基于游标的记录 [英] Cursor based records in PostgreSQL
本文介绍了PostgreSQL中基于游标的记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图对连接多个表的查询使用游标。我已经看到,对于oracle有一个基于游标的记录。当我尝试相同的Postgres,它抛出一些错误。如何在Postgres中做同样的事?
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?
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: relation "xyz" does not exist
CONTEXT: compilation of PL/pgSQL function "avoidable_states" near line 4
推荐答案
只需使用 / code> type:
Just use the RECORD
type:
DECLARE
...
cur_row RECORD;
BEGIN
...
FETCH xyz INTO cur_row;
EXIT WHEN NOT FOUND;
IF cur_row.city LIKE 'CH%' THEN
...
这篇关于PostgreSQL中基于游标的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文