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.

Lord Elrond · Accepted Answer · 2024-08-15 22:53:19Z

16

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)

edited Aug 15, 2024 at 22:53

answered Jul 14, 2021 at 16:51

Lord Elrond

16.5k8 gold badges54 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kerfuffle Over a year ago

This feels buggy. When using the function with psql, the output is as expected. When accessing the function via Python, the output equals the input.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related