cursor.fetchall() returns only default value of function's cursor (not running fetch all in)

Question

I am having a little bit of a trouble trying to pull my data through a function I've created in my database (using postgresql + postgis). The case is, whenever I run my query, which can be found in my function via sql, separately, it runs just fine and returns my data.

My function looks like this

CREATE OR REPLACE FUNCTION common.myfunc(string) RETURNS refcursor AS 
    $BODY$ 
    DECLARE
         ref refcursor default 'outrefcursor' 
    BEGIN
         OPEN ref FOR

         -- pretty big query here

         RETURN ref; END;
    $BODY$   LANGUAGE plpgsql VOLATILE

When I run

SELECT common.myfunc(string);
FETCH ALL IN outrefcursor;

It works just fine

However, on my django side when I do

cursor.execute("SELECT * FROM common.myfunc(someString)")
mydata = cursor.fetchall()

it return the default value of the cursor

I'd truly appreciate any help to solve this strange behavior

score 0 · Accepted Answer · answered Jan 27 '16 at 17:34

Got it solved. The deal is: if you need to run a sql function and then fetch all in cursor, you have to do it explicitly and atomically.

For example,

You run your function

SELECT common.myfunc(string);
FETCH ALL IN outrefcursor;

First, django's cursor.fetchall() won't do the FETCH ALL IN --- for you. It has to be explicit as a command. However, if you run cursor.execute("SELECT * FROM common.myfunc(someString)"), django will automatically commit this command and lose track of the cursor it got returned from the query. The trick is to wrap your view with @transaction.atomic

Follows the example,

@transaction.atomic
def func(request):
cursor = connection.cursor()
    try:
        cursor.execute("SELECT myfunc('"+ parameter +"')")
        cursor.execute('FETCH ALL IN "outrefcursor"')
        data = cursor.fetchall()
    finally:
        cursor.close()

cursor.fetchall() returns only default value of function's cursor (not running fetch all in)

1 Answers1