Returning result set from redshift stored procedure

Question

I have a procedure that returns a recordset using the cursor method:

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
/

And I can call it perfectly fine from workbench using my admin login.

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

Result is a dataset of 16columns x >7k rows

(To be honest I don't even need to do the transaction parts, it'll run just fine with the CALL only)

However, when I call it via psycopg2, this is what happens :

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

Output:

> rows:[('apples',)]

I've played around with using the commit and not using it. Just really struggling to understand what is going on. At this stage not sure if it's how I'm calling from Python, or on the Redshift side of things.

Any guidance appreciated !

Jeane

Answer 1

The procedure receives a name as its argument and returns a server-side cursor with that name. On the client side, after calling the procedure you must declare a named cursor with the same name and use it to access the query results. You must do this before committing the connection, otherwise the server-side cursor will be destroyed.

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)

This is analogous to how you might get the results in the psql console, as described in the Redshift docs:

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

Wersh