从Redshift存储过程返回结果集。

Question

我有一个使用游标方法返回记录集的过程：

CREATE OR REPLACE PROCEDURE myschema.permissions_sp(rs_out INOUT refcursor)
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $$
BEGIN
  OPEN rs_out FOR select schema_name,schema_owner,grantee_type,grantee,p_usage,p_create,object_name,perms,p_select,p_update,p_insert,p_delete,p_truncate,p_references,p_trigger,p_rule from xxxx.myview;
END;
$$ 
/
GRANT EXECUTE on PROCEDURE myschema.permissions_sp(INOUT refcursor)  TO xxxx_user
/

我可以使用我的管理员登录从工作台完美地调用它。

BEGIN;
CALL meta.permissions_sp('apples'); 
COMMIT;

结果是一个数据集，包含16个列和超过7k行

(说实话，我甚至不需要执行事务部分，只用CALL就可以正常运行)

然而，当我通过psycopg2调用它时，会发生以下情况:

cur = conn.cursor()
cur.execute("CALL meta.permissions_sp('apples');")
conn.commit()
rows = cur.fetchall()
print('rows:%s' % (rows))

输出：

> rows:[('apples',)]

我已经尝试过使用提交和不使用提交，但仍然很难理解发生了什么。目前不确定是在Python端调用的问题还是Redshift方面的问题。

非常感谢任何指导！

- SimonB

Answer 1

该过程接收一个名称作为参数，并返回一个带有该名称的服务器端游标。在客户端上，在调用该过程后，您必须声明一个与该名称相同的命名游标并使用它来访问查询结果。您必须在提交连接之前执行此操作，否则服务器端游标将被销毁。

with psycopg2.connect(dbname='test') as conn:
    cur = conn.cursor()
    cur.execute("""CALL myschema.permissions_sp('mycursor')""")
    
    # Use the name that was passed to the procedure.
    named_cursor = conn.cursor('mycursor')
    for row in named_cursor.fetchall():
        print(row)

这类似于在psql控制台中获取结果的方式，如Redshift文档所述：

BEGIN;
CALL myschema.permissions_sp('mycursor');
FETCH ALL FROM mycursor;
COMMIT;

- snakecharmerb