How to Create PL/pgSQL Functions (PSQL) to Handle Odoo 18 Operations

Odoo 18 is the latest iteration of Odoo's open-source ERP platform, offering a wide range of integrated business applications such as CRM, Sales, Inventory, Accounting, HR, and more. With each version, Odoo evolves to provide better performance, enhanced usability, and extended features tailored for both small and large enterprises. Odoo 18 continues this trend, focusing on improved automation, real-time collaboration, and refined backend performance. While Odoo primarily uses Python for its business logic and XML/QWeb for its user interface, it relies heavily on PostgreSQL as its backend database.

PL/pgSQL (Procedural Language/PostgreSQL) is PostgreSQL’s built-in procedural language used to create powerful and reusable SQL-based functions. These functions allow developers and database administrators to execute logic on the database layer itself, reducing the need for repetitive client-server communication. By leveraging PL/pgSQL, complex operations such as record manipulation, triggers, validations, and reporting logic can be executed directly within the database.

For example, suppose you want to automatically manage inactive customers in Odoo 18 by archiving those who haven’t made any sales in the past two years. Over time, these dormant customer records can accumulate and clutter your database, reducing system efficiency and data relevance. Instead of identifying and deactivating such customers manually, you can use a PL/pgSQL function in PostgreSQL to perform this operation directly.

Data Update Function

Here’s a simple function that archives (sets active = FALSE) all customers who have not made a sale order in over 2 years:

PSQL Function:

CREATE OR REPLACE FUNCTION archive_customers_with_no_sales()
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;

Here’s what this function does:

It scans all customer records (res_partner) that are currently active and checks the latest sale order date associated with each customer. If no sale orders exist for a customer (MAX(so.date_order) IS NULL), or if the most recent sale was placed over two years ago, the function updates the record by setting active = FALSE. This effectively archives those customer records in Odoo, helping maintain a cleaner and more efficient customer database.

To run this function, simply execute:

SELECT archive_customers_with_no_sales();

This function helps you automatically clean up and streamline your customer data without needing to perform the task manually through the Odoo interface.

Reporting Function

For another example, let’s say you want to build a quick sales report based on confirmed sale orders (state = 'sale') and show key details like the customer name, number of orders, and total sales value. You can do this with a PL/pgSQL function that returns a table instead of just a single value.

PSQL Function:

CREATE OR REPLACE FUNCTION simple_sales_summary()
RETURNS TABLE (
    customer TEXT,
    total_orders INTEGER,
    total_sales NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        rp.name::TEXT AS customer,
        COUNT(so.id)::INTEGER AS total_orders,
        SUM(so.amount_total) AS total_sales
    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_sales DESC;
END;
$$ LANGUAGE plpgsql;

This PL/pgSQL function, simple_sales_summary(), provides a quick summary of sales performance by customer. When executed, it returns a table showing each customer's name, the total number of confirmed sale orders (so.state = 'sale'), and the total value of those orders.

To retrieve the results, simply run:

SELECT * FROM simple_sales_summary();

You can run this function from either pgAdmin or the psql terminal, and it will return the results in a table layout, similar to a report, as shown below. If you're using pgAdmin, you can also export the results to a CSV file and convert it to XML or other formats as needed.

PL/pgSQL functions offer a powerful way to manage and enhance Odoo 18 operations directly through the PostgreSQL database. They enable actions such as record updates, automatic field mapping, data cleanup, and generating a variety of reports, including those for sales, inventory, customer engagement, or finance. These functions can be triggered manually from tools like pgAdmin or the PostgreSQL  command line, and the results can easily be saved in CSV or XML formats for reporting or integration needs. By using this method, businesses can streamline repetitive tasks, enhance system efficiency, and gain more direct control over their Odoo data without being limited to the standard Odoo interface.

To read more about How to Handle CSV File Operations in Odoo 18, refer to our blog How to Handle CSV File Operations in Odoo 18.