[Answered ]-Get multiple postgresql function returned cursors from Django

by

1👍

✅

Experimentally, this seems to do the trick with bare psycopg2. The cursor provided by Django should be more or less compatible with a psycopg2 cursor:

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)

The output is

[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]

quite as expected.

👤AKX

Leave a comment