How to Create PL/pgSQL Functions for Odoo 19 Operations

PL/pgSQL-that’s PostgreSQL’s own scripting language-lets you build logic right inside the database instead of handling everything through the app. It’s handy for jobs like checking data, running triggers, cleaning records, or automating reports, because everything happens where the data already lives.

For example, in Odoo 19, you might notice that some customer profiles just sit there with no purchases for a couple of years. Over time, they clutter the system and make things run slower. Rather than digging through and archiving them one by one, you could write a short PL/pgSQL script that does it automatically every so often. It keeps your data tidy and saves you from repetitive admin work.

Data Update Function

Here’s a quick example of a PostgreSQL function you can use to automatically mark vendors as inactive if they haven’t made a purchase order in the last two years. It just flips their “active” flag to false, so old records don’t clutter the system.

PSQL Function:

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

This little function loops through all vendors that are still marked active in res_partner. It checks when each one last placed a purchase order. If there’s no record at all, or the last order date is older than 2 years, it just flips their active flag off.

Basically, it’s a cleanup job - keeps old vendor records from piling up in Odoo.

You can just run the function in psql to get it done:

select archive_vendors_with_no_purchases();

This function makes it easy to keep your vendor list clean without having to go into Odoo and manually update every record.

Reporting Function

Here’s another quick example, this time working with purchase orders instead of sales. Let’s say you just want a small report showing confirmed purchase orders - basically the vendor’s name, how many orders they’ve made, and the total amount they’ve spent.

You can pull that together with a simple PL/pgSQL function that returns a table shown below.

PSQL Function:

CREATE OR REPLACE FUNCTION simple_purchase_summary()
RETURNS TABLE (
    vendor TEXT,
    total_orders INTEGER,
    total_purchases NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        rp.name::TEXT AS vendor,
        COUNT(po.id)::INTEGER AS total_orders,
        SUM(po.amount_total) AS total_purchases
    FROM purchase_order po
    JOIN res_partner rp ON po.partner_id = rp.id
    WHERE po.state = 'purchase'
    GROUP BY rp.name
    ORDER BY total_purchases DESC;
END;
$$ LANGUAGE plpgsql;

This function makes it easy to keep your vendor list clean without having to go into Odoo and manually update every record. Here’s another quick example, this time working with purchase orders instead of sales. Let’s say you just want a small report showing confirmed purchase orders - basically the vendor’s name, how many orders they’ve made, and the total amount they’ve spent. You can pull that together with a simple PL/pgSQL function that returns a table.

To get the results, simply execute:

select * from simple_purchase_summary();

You can run this function using either pgAdmin or the psql terminal. It will show the results in a table format, similar to a report. In pgAdmin, you can also export the results to a CSV file. You can then convert this file to XML or other formats if needed.

PL/pgSQL is a useful tool for managing tasks directly in Odoo 19’s database. You can automatically update records, fill in fields, remove old data, or create fast reports on purchases, sales, inventory, or customers. All you need to do is run the functions in pgAdmin or the PostgreSQL command line, then export the results in CSV, XML, or another format you need. It makes your work easier by cutting down on repetitive tasks, ensuring everything runs well, and giving you more control over your Odoo data than just using the regular interface.

To read more about How to Create PL/pgSQL Functions (PSQL) to Handle Odoo 18 Operations, refer to our blog How to Create PL/pgSQL Functions (PSQL) to Handle Odoo 18 Operations.