I have a stored function in postgres:
CREATE FUNCTION return_curs() RETURNS refcursor AS $$
BEGIN
OPEN cursor_x FOR SELECT col FROM table_y;
RETURN cursor_x;
END; $$
Then in Python I want to invoke the procedure to fetch returnung cursor row by row for exapmle using psycopg2. Are there any way to perform this? Thanks.
It is easier to let psycopg2 do the server side cursor creation work just by naming it:
cursor = conn.cursor(name='cursor_x')
query = "select * from t"
cursor.execute(query)
for row in cursor:
print row
To use a returning cursor function execute it as usual:
cur = conn.cursor()
cur.callproc('return_curs')
Then catch the returned cursor with a named cursor:
named_cursor = conn.cursor(name='cursor_x')
for row in named_cursor:
print row
