Stored Procedures and Django

Since it is not always possible, or efficient, to accomplish the database queries you would like using the Django Database API, many developers will find themselves needing to escape the constraints of the API and leverage stored procedures.

For scenarios when you would like to execute stored procedures, or any other custom SQL, you can use the Python DB API as suggested here in the Django documentation.

So, to begin, let’s examine the following stored procedure. This example was taken from the Postgres documentation on Cursors.

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test; RETURN $1; END; ' LANGUAGE plpgsql;
This stored procedure takes the name of a refcursor as an argument, executes a SELECT, and returns a refcursor containing the results of the SELECT. Using the tool psql the stored procedure can be executed in the following way.
BEGIN; SELECT reffunc('funccursor'); FETCH ALL IN funccursor; COMMIT;
It is important that this stored procedure is wrapped in a transaction to be able to FETCH the contents of the returned refcursor.

Now, let’s examine how you’d accomplish this in Django
from django.db import connection cursor = connection.cursor() cursor.execute("BEGIN") cursor.execute("SELECT reffunc('funccursor')") cursor.execute("FETCH ALL IN funccursor") results = cursor.fetchall() cursor.execute("COMMIT")
Simple! Now, the contents of the refcursor, in this case funccursor are in results.