Get multiple postgresql function returned cursors from Django-CodePudding

I'm new to django and not able to figure out if it is possible to retrieve multiple cursors returned by a postgresql function.

Edit: to return multiple cursors from postgresql function:

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

Further in the function body:

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

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

RETURN;

I've been searching the net for hours now and haven't been able to find a single example.

I plan to write raw sql from django (no ORM) to call the postgresql functions.

Anyone to enlighten me?

Thanks

CodePudding user response：

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

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:
            # Must use f-strings instead of interpolation since `name` is an identifier,
            # not a string.
            curs.execute(f"FETCH ALL FROM \"{name}\"")
            print([r[0] for r in curs])

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.