How to Create PL/pgSQL Functions to Handle Odoo 18 Operations

Working directly with your database gives you incredible power to automate tasks, run complex calculations, and generate custom reports. While Odoo 18 provides an excellent Python-based framework for business logic, there are times when executing operations directly at the database level using PL/pgSQL can dramatically improve performance and simplify maintenance.

This comprehensive guide explores how to create powerful PL/pgSQL functions in PostgreSQL to handle Odoo 18 operations. Whether you're cleaning up inactive records, generating custom reports, or automating data transformations, you'll learn practical techniques that work in real-world scenarios.

Why Use PL/pgSQL with Odoo 18?

Odoo 18 is built on top of PostgreSQL, one of the most powerful open-source relational databases available. While most business logic in Odoo is written in Python, there are compelling reasons to leverage PL/pgSQL for certain operations:

Understanding PL/pgSQL Basics

PL/pgSQL (Procedural Language/PostgreSQL) is PostgreSQL's native procedural programming language. It extends SQL with control structures, variables, and exception handling, making it ideal for creating reusable database functions.

Key Components of a PL/pgSQL Function

Use Case 1: Archiving Inactive Customers

Let's start with a practical example. Over time, your Odoo database accumulates customer records that are no longer active. These might be customers who haven't placed an order in years. Keeping them active clutters your customer lists and can slow down searches and reports.

Here's a PL/pgSQL function that automatically archives customers who haven't made a purchase in the last two years:

CREATE OR REPLACE FUNCTION archive_inactive_customers()
RETURNS VOID AS $$
BEGIN
    UPDATE res_partner
    SET active = FALSE
    WHERE id IN (
        SELECT rp.id
        FROM res_partner rp
        LEFT JOIN sale_order so ON so.partner_id = rp.id
        GROUP BY rp.id
        HAVING MAX(so.date_order) IS NULL
            OR MAX(so.date_order) < NOW() - INTERVAL '2 years'
    )
    AND active = TRUE;
END;
$$ LANGUAGE plpgsql;

How This Function Works

Executing the Function

To run this function, simply execute the following SQL command:

SELECT archive_inactive_customers();

Use Case 2: Creating a Sales Summary Report

Reporting is another powerful use case for PL/pgSQL functions. Instead of building complex Python code or using the Odoo reporting framework, you can create a function that returns a structured dataset directly from the database.

Here's a function that generates a sales summary showing customer performance based on confirmed orders:

CREATE OR REPLACE FUNCTION generate_sales_summary()
RETURNS TABLE (
    customer_name TEXT,
    order_count INTEGER,
    total_revenue NUMERIC,
    avg_order_value NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        rp.name::TEXT AS customer_name,
        COUNT(so.id)::INTEGER AS order_count,
        COALESCE(SUM(so.amount_total), 0) AS total_revenue,
        COALESCE(AVG(so.amount_total), 0) AS avg_order_value
    FROM sale_order so
    JOIN res_partner rp ON so.partner_id = rp.id
    WHERE so.state = 'sale'
    GROUP BY rp.name
    ORDER BY total_revenue DESC;
END;
$$ LANGUAGE plpgsql;

Function Breakdown

Running the Report

Execute the function to retrieve the sales summary:

SELECT * FROM generate_sales_summary();

The results will be displayed in a table format showing customer performance metrics. You can export these results to CSV directly from pgAdmin or the psql terminal for further analysis or integration with other tools.

Advanced Example: Parametrized Date Range Report

Let's create a more flexible reporting function that accepts date range parameters:

CREATE OR REPLACE FUNCTION sales_by_date_range(
    start_date DATE,
    end_date DATE
)
RETURNS TABLE (
    order_date DATE,
    customer TEXT,
    order_reference VARCHAR,
    amount NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        so.date_order::DATE AS order_date,
        rp.name::TEXT AS customer,
        so.name::VARCHAR AS order_reference,
        so.amount_total AS amount
    FROM sale_order so
    JOIN res_partner rp ON so.partner_id = rp.id
    WHERE so.state = 'sale'
        AND so.date_order >= start_date
        AND so.date_order <= end_date
    ORDER BY so.date_order DESC;
END;
$$ LANGUAGE plpgsql;

Execute with specific date parameters:

-- Get sales for Q4 2024
SELECT * FROM sales_by_date_range('2024-10-01', '2024-12-31');

Executing PL/pgSQL Functions

Using pgAdmin

1. Connect to Your Database: Open pgAdmin and connect to your Odoo PostgreSQL database.
2. Open Query Tool: Right-click on your database and select "Query Tool".
3. Create the Function: Paste your PL/pgSQL function code and execute it.
4. Run the Function: Execute the SELECT statement to run your function.
5. Export Results: Right-click on the results grid and choose "Export" to save as CSV, XML, or other formats.

Using psql Command Line

# Connect to database
psql -U odoo -d your_database_name

# Execute function
SELECT * FROM generate_sales_summary();

# Export to CSV
\copy (SELECT * FROM generate_sales_summary()) TO '/path/to/sales_report.csv' CSV HEADER;

Best Practices for PL/pgSQL in Odoo

Common Use Cases for PL/pgSQL in Odoo

Frequently Asked Questions

Conclusion

PL/pgSQL functions provide a powerful toolkit for managing Odoo 18 operations at the database level. Whether you're automating data cleanup, generating custom reports, or performing bulk updates, these functions can dramatically improve performance and simplify maintenance tasks.

By working directly with PostgreSQL, you gain precise control over your data and can execute operations that would be cumbersome or slow through Odoo's application layer. The key is understanding when to use database-level operations and when to rely on Odoo's ORM, ensuring you maintain data integrity while maximizing efficiency.

Start with simple functions like the examples in this guide, test thoroughly in development environments, and gradually build more sophisticated automation as you become comfortable with PL/pgSQL. Your Odoo system will thank you with improved performance and easier data management.