How to Create PL/pgSQL Functions for Odoo 18 Operations: Complete Guide
By Braincuber Team
Published on March 11, 2026
Running heavy data operations through Odoo's ORM can bring your server to its knees. If you need to archive 50,000 inactive customers or generate a summary report across 2 million sale order lines, doing it in Python is the slow way. The fast way? Pushing the logic down to the database layer using PostgreSQL's built-in procedural language: PL/pgSQL. This skips the ORM entirely, executing operations instantly at the database level.
What You'll Learn:
- Why use PL/pgSQL instead of Python/ORM for Odoo operations
- How to write a PL/pgSQL function to update/archive Odoo records
- How to write a PL/pgSQL function that returns a custom reporting table
- How to execute these functions via pgAdmin or psql terminal
Why Bypass the Odoo ORM?
Odoo 18 primarily uses Python for business logic and XML/QWeb for the UI. But beneath that sits a robust PostgreSQL database. While Odoo’s ORM is great for standard CRUD operations and maintaining strict security/access rights, it introduces overhead during bulk operations.
Massive Performance Gains
PL/pgSQL executes logic directly on the database server. This eliminates the client-server communication overhead and the memory costs of instantiating thousands of Python objects.
Direct Data Manipulation
Perfect for raw data cleanup, complex cross-table validations, and generating fast aggregate reports that don't need to trigger Odoo's automated actions or compute fields.
Warning: The ORM is Blind to SQL
When you update records via PL/pgSQL, you bypass Odoo's security rules, auto-computed fields, and Python write() triggers. Use this strictly for administrative bulk updates or read-only reporting. Do not use this to create complex transactions like invoices or inventory moves.
Example 1: Bulk Data Update Function
The Problem: Over time, dormant customer records clutter your database, slowing down autocomplete searches and bloating views. You want to automatically archive (set active = FALSE) any customer who hasn't placed an order in over 2 years, or has never placed an order at all.
The PL/pgSQL Solution: We create a function that updates the res_partner table based on aggregate data from the sale_order table.
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;
Define the Function
The script creates (or replaces) a function named archive_customers_with_no_sales that returns VOID (no data returned, just executes an action).
Execute the Update
It runs an UPDATE query against the res_partner table to set active = FALSE for targeted record IDs.
Filter the Records
The WHERE clause performs a LEFT JOIN to check the maximum (most recent) date_order for each partner. If it's NULL (never ordered) or older than 2 years, they get archived.
How to Execute It
Once the function is created in the database, you can run it manually from pgAdmin or the psql terminal anytime you want to clean up your database:
SELECT archive_customers_with_no_sales();
Example 2: Custom SQL Reporting Function
The Problem: You need a highly specific sales aggregate report showing total orders and lifetime value per customer. Pulling this via Odoo's interface requires loading thousands of individual order records into memory just to sum them up.
The PL/pgSQL Solution: We create a function that returns a virtual table (instead of a single value). The database does the heavy lifting and math in milliseconds, simply handing back the final row data.
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;
Key Differences in this Function
| Syntax Feature | Explanation |
|---|---|
RETURNS TABLE | Instead of returning VOID, you explicitly define the structure of the virtual table being returned (column names and data types). |
RETURN QUERY | Tells PL/pgSQL to execute the following SELECT statement and return the resulting rows matching the TABLE definition. |
::TEXT Type Casting | Odoo stores names as VARCHAR, but we defined our return table to expect TEXT. The :: syntax forces the PostgreSQL type conversion. |
How to Execute It
Because this function returns a table (not just a single action), you execute it like a standard SELECT statement querying a table:
SELECT * FROM simple_sales_summary();
Running this through pgAdmin provides the results in a clean grid view, which can be immediately exported to CSV or inserted into external BI tool pipelines via standard database connections.
Frequently Asked Questions
Can PL/pgSQL functions trigger Odoo automated actions?
No. PL/pgSQL operates directly on the PostgreSQL database. It completely bypasses Odoo's ORM, meaning Python computes, tracking fields, constraints, and automated actions will not run.
Is it safe to update Odoo data using SQL?
It is safe for simple administrative flags (like setting active=False) or generic status strings. It is highly dangerous for complex workflow states (like confirming an invoice) because it skips essential accounting validations built into Python.
How do I run these functions automatically?
You can wrap the SQL execution inside a Python Scheduled Action in Odoo using self.env.cr.execute("SELECT your_function()"), or execute them directly via pgAgent jobs in PostgreSQL.
Can I export the results of a PL/pgSQL table function?
Yes. Using tools like pgAdmin, you can execute the SELECT statement and immediately click the "Download as CSV" button to export the result set.
Do I need server access to create PL/pgSQL functions?
Yes. You need direct database access (via psql or pgAdmin) to define these functions. Odoo's standard frontend interface does not support creating PostgreSQL procedural functions.
Is Your Odoo Database Grinding to a Halt?
Slow reports, timeout errors during bulk updates, and locking issues all point to database inefficiencies. Braincuber specializes in PostgreSQL optimization and data restructuring for scaling D2C brands. Let's fix your backend.