How to use PostgreSQL's stored procedures or functions in Django project

Question

I am working on one Django project. And I decided to write logic code in PostgreSQL instead of writing in Python. So, I created a stored procedure in PostgreSQL. For example, a stored procedure looks like this:

create or replace procedure close_credit(id_loan int)
language plpgsql
as $$
begin
    update public.loan_loan
    set sum = 0
    where id = id_loan;
    commit;
end;$$

Then in settings.py, I made the following changes:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'pawnshop',
        'USER': 'admin',
        'PASSWORD': password.database_password,
        'HOST': 'localhost',
        'PORT': '',
    }
}

So the question is, How can I call this stored procedure in views.py?

p.s.

Maybe it sounds like a dumb question, but I really couldn't find any solution in Django.

Answer 1

I'd recommend storing the procedure definition in a migration file. For example, in the directory myapp/migrations/sql.py:

from django.db import migrations

SQL = """
CREATE PROCEDURE close_credit(id_loan int)
language plpgsql
AS $$
BEGIN
    UPDATE public.loan_loan
    SET sum = 0
    WHERE id = id_loan;
    COMMIT;
END; $$
"""

class Migration(migrations.Migration):
    
    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [migrations.RunSQL(SQL)]

Note: you will need to replace myapp with the name of your application, and you will need to include only the most recent migration file for your app as a dependency.

Now you can install the procedure using python3 manage.py migrate.

Once your procedure is defined in the database, you can call it using cursor.callproc:

from django.db import connection

def close_credit(id_loan):
    with connection.cursor() as cursor:
        cursor.callproc('close_credit', [id_loan])

All that being said, if your procedure is really as trivial as the example you provided, it would be better to use the ORM:

Loan.objects.filter(id=id_loan).update(sum=0)