PostgreSQL 中基于游标的记录 [英] Cursor based records in PostgreSQL

问题描述

我正在尝试将游标用于连接多个表的查询.我已经看到对于 oracle 有一个基于游标的记录.当我为 Postgres 尝试同样的方法时，它会引发一些错误.我如何在 Postgres 中做同样的事情?

创建或替换函数avoidable_states()返回 SETOF varchar AS$BODY$宣布xyz CURSOR FOR select * from address ad在 ad.city_id = ct.city_id 上加入 city ct；xyz_row 记录；开始打开 xyz；环形将 xyz 提取到 xyz_row 中；xyz_row = null 时退出；如果 xyz_row.city 像 '%hi%' 那么返回下一个 xyz_row.city;万一;结束循环；关闭 xyz；结尾;$BODY$语言 plpgsql 易失性；

我得到的错误是:

<块引用>

错误:关系xyz"不存在上下文:第 4 行附近的 PL/pgSQL 函数avoidable_states"的编译

解决方案

只需使用 RECORD 类型:

DECLARE...cur_row 记录；开始...FETCH xyz INTO cur_row;找不到时退出；IF cur_row.city LIKE 'CH%' THEN...

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 I get is:

ERROR:  relation "xyz" does not exist
CONTEXT:  compilation of PL/pgSQL function "avoidable_states" near line 4

解决方案

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屋！