如何在Django中调用PostgreSQL存储过程并在Web应用展示结果？

Hey, great job getting all the CRUD operations working with Django and PostgreSQL! Calling stored procedures/functions and displaying the results in your web app is totally doable—let me walk you through it step by step:

First off, let's confirm you have a valid procedure or function in PostgreSQL. For example, here's a simple function that returns active users from the built-in auth_user table:

-- Create a function that returns a table of active users
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE(id INT, username VARCHAR, email VARCHAR) AS $$
BEGIN
    RETURN QUERY SELECT id, username, email FROM auth_user WHERE is_active = TRUE;
END;
$$ LANGUAGE plpgsql;

If you need a procedure (for write operations like updates), here's an example:

-- Create a procedure to update a user's active status
CREATE OR REPLACE PROCEDURE update_user_active_status(user_id INT, new_status BOOLEAN)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE auth_user SET is_active = new_status WHERE id = user_id;
END;
$$;

There are two main ways to call PostgreSQL procedures/functions in Django—let's cover both:

Option 1: Use connection.cursor() (Most Flexible) #

This is the go-to method for most cases, especially when dealing with procedures that return result sets or have side effects.

For Functions That Return Data #

Create a utility function (maybe in utils.py) to fetch results:

from django.db import connection

def get_active_users_from_function():
    with connection.cursor() as cursor:
        # Call the function with a SELECT statement
        cursor.execute("SELECT * FROM get_active_users();")
        
        # Extract column names from the cursor description
        column_names = [col[0] for col in cursor.description]
        # Convert raw row tuples to dictionaries (easier to use in templates)
        results = [dict(zip(column_names, row)) for row in cursor.fetchall()]
    
    return results

For Procedures (Write Operations) #

If you're calling a procedure that modifies data:

from django.db import connection

def update_user_status(user_id, new_status):
    with connection.cursor() as cursor:
        # Use CALL for PostgreSQL procedures
        cursor.execute("CALL update_user_active_status(%s, %s);", [user_id, new_status])
    
    # Explicitly commit if your procedure modifies data (Django auto-commits by default, but safe to confirm)
    connection.commit()

Option 2: Use Django ORM's Func (For ORM Integration) #

If you want to tie the function call directly into your ORM queries, you can wrap it with a Func class:

from django.db.models import Func, IntegerField, CharField
from django.contrib.auth.models import User

class GetActiveUsers(Func):
    function = 'get_active_users'
    # Define the output fields matching your function's return type
    output_fields = [
        IntegerField(),  # id
        CharField(),     # username
        CharField()      # email
    ]

# Call it via the ORM
active_users = User.objects.annotate(
    user_data=GetActiveUsers()
).values_list('id', 'username', 'email')

Note: This works best for functions that return scalar values or fit into ORM workflows—for full result sets, the cursor method is usually simpler.

Now that you can fetch the data, pass it to a view and render it in a template:

Step 1: Create a View #

from django.shortcuts import render
from .utils import get_active_users_from_function

def active_users_page(request):
    active_users = get_active_users_from_function()
    # Pass the results to your template
    return render(request, 'active_users.html', {'active_users': active_users})

Step 2: Create a Template (active_users.html) #

<h1>Active Users</h1>
<table border="1">
    <thead>
        <tr>
            <th>ID</th>
            <th>Username</th>
            <th>Email</th>
        </tr>
    </thead>
    <tbody>
        {% for user in active_users %}
        <tr>
            <td>{{ user.id }}</td>
            <td>{{ user.username }}</td>
            <td>{{ user.email }}</td>
        </tr>
        {% empty %}
        <tr>
            <td colspan="3">No active users found.</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

• Permissions: Ensure your Django database user has EXECUTE permissions on the procedure/function in PostgreSQL.
• Transactions: If your procedure modifies data, make sure transactions are handled correctly (Django's default auto-commit works for most cases, but you can use transaction.atomic() if needed).
• Result Formatting: Converting cursor results to dictionaries makes them much easier to work with in templates compared to raw tuples.

内容的提问来源于stack exchange，提问作者franchesco totti