从Django获取多个postgresql函数返回的游标

Question

我是django的新手，不知道是否可以检索postgresql函数返回的多个游标。

编辑：从postgresql函数返回多个游标：

CREATE FUNCTION function_name(parameters) RETURNS SETOF refcursor... 

进一步在功能体中：

OPEN cursor1
- - some code to fill the cursor1
RETURN NEXT cursor1;
OPEN cursor2 
- - some code to fill the cursor2
RETURN NEXT cursor2;
RETURN;

我已经在网上搜索了好几个小时，没有找到一个例子。

我计划从django(没有ORM(编写原始sql来调用postgresql函数。

有人能启发我吗？

感谢

Answer 1

从实验上看，这似乎对裸psycopg2起到了作用。Django提供的游标应该或多或少与psycopg2游标兼容：

import psycopg2
from psycopg2 import sql
create_sql = """
CREATE OR REPLACE FUNCTION multi_cur() RETURNS SETOF refcursor AS $$
DECLARE cursor1 refcursor;
DECLARE cursor2 refcursor;
BEGIN
OPEN cursor1 FOR SELECT x * x FROM generate_series(1, 10) AS x;
RETURN NEXT cursor1;

OPEN cursor2 FOR SELECT SQRT(x) FROM generate_series(1, 10) AS x;
RETURN NEXT cursor2;
END;
$$ LANGUAGE plpgsql;
"""
with psycopg2.connect(dbname='postgres') as conn:
with conn.cursor() as curs:
curs.execute(create_sql)
curs.execute("SELECT * FROM multi_cur()")
# Get the cursor names (['<unnamed portal 1>', '<unnamed portal 2>'])
cursor_names = [r[0] for r in curs]
# Fetch all results from those cursors
for name in cursor_names:
curs.execute(sql.SQL("FETCH ALL FROM {}").format(sql.Identifier(name)))
print([r[0] for r in curs])
for row in curs:
print(row)

输出为

[1, 4, 9, 16, 25, 36, 49, 64, 81, 100]
[1.0, 1.4142135623730951, 1.7320508075688772, 2.0, 2.23606797749979, 2.449489742783178, 2.6457513110645907, 2.8284271247461903, 3.0, 3.1622776601683795]

正如预期的那样。