How to Use the PL/Python Language in PostgreSQL

Abdul Wahab Junaid

3 years ago

How to Use the PL/Python Language in PostgreSQL

Of all the alternative procedural languages available in PostgreSQL, PL/Python is the one I reach for most often when PL/pgSQL starts feeling limiting. Python’s massive standard library, its readability, and its dominance in data science and machine learning workflows make it a natural fit for database functions that need to do more than simple SQL manipulation — think statistical calculations, calling out to external APIs, or working with structured data formats like JSON in ways that feel far more natural in Python than in SQL.

In this guide, I’ll explain what PL/Python is, how to install and enable it, how function syntax and parameter passing work, practical real-world examples, common pitfalls, and the best practices I follow to keep PL/Python functions safe, fast, and maintainable.

What Is PL/Python?

PL/Python is a procedural language extension that ships with PostgreSQL’s standard distribution (though it needs to be installed as a separate package on most Linux distros) and allows me to write functions and triggers using Python syntax. Like PL/Perl, it comes in trusted and untrusted flavors:

plpython3u — the modern, Python 3-based variant. Note that as of current PostgreSQL versions, PL/Python is only offered in an untrusted form (plpython3u), meaning it requires superuser privileges to install and to grant usage rights, since it allows full filesystem and network access.

This is a meaningful distinction from PL/Perl or PL/Tcl, which offer separate trusted sandboxed variants. PostgreSQL’s own documentation notes this limitation explicitly, and it affects how I think about access control when using PL/Python in a shared or multi-tenant database.

Why Choose PL/Python?

I find myself reaching for PL/Python specifically when:

I need access to Python’s ecosystem. Libraries for statistics, machine learning inference, JSON/XML handling, and HTTP requests are far more mature in Python than what’s natively available in PL/pgSQL.
The logic is inherently procedural and complex. Multi-step algorithms with lots of branching logic tend to read more clearly in Python than in PL/pgSQL’s more verbose control-flow syntax.
Data science teams are already working in Python. If a data science team has already prototyped scoring logic or a small model in Python, PL/Python offers a path to run that logic directly against the data without an intermediate export/import step.
JSON manipulation. Even though PostgreSQL has strong native JSON support, sometimes the transformation logic needed is complex enough that Python’s json module and general dictionary handling is more convenient.

Installing PL/Python

On Debian/Ubuntu-based systems:

sudo apt-get install postgresql-plpython3-16

Once the OS-level package is installed, I enable it per database as a superuser:

CREATE EXTENSION plpython3u;

I can confirm the language is available with:

SELECT lanname FROM pg_language WHERE lanname = 'plpython3u';

Because it’s untrusted-only, only superusers can create this extension, and by default only superusers can create functions using it. If I want to allow specific non-superuser roles to write PL/Python functions, I need to explicitly grant USAGE on the language:

GRANT USAGE ON LANGUAGE plpython3u TO trusted_developer_role;

I do this cautiously, since it effectively grants that role the ability to execute arbitrary Python code — including filesystem and network access — from inside the database server process.

Basic Syntax of a PL/Python Function

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
    return a + b
$$ LANGUAGE plpython3u;

Function parameters are automatically bound as regular Python variables matching their SQL parameter names — no unpacking from an args array required, unlike PL/Perl or PL/PHP. This is one of the small but meaningful ergonomic advantages PL/Python has.

SELECT add_numbers(12, 30);

Working with Parameters and Return Types

Scalar Values

CREATE OR REPLACE FUNCTION greet_user(name TEXT)
RETURNS TEXT
AS $$
    if name is None:
        return "Hello, stranger!"
    return f"Hello, {name.strip()}! Great to see you."
$$ LANGUAGE plpython3u;

SELECT greet_user('  Priya  ');
-- Hello, Priya! Great to see you.

Returning Sets

For SETOF functions, I can either return a list, or use a Python generator with yield, which is more memory-efficient for large result sets:

CREATE OR REPLACE FUNCTION list_even_numbers(max_val INTEGER)
RETURNS SETOF INTEGER
AS $$
    for i in range(0, max_val + 1, 2):
        yield i
$$ LANGUAGE plpython3u;

SELECT * FROM list_even_numbers(10);

Returning Composite Types

I return a Python dictionary (or list of dictionaries for multiple rows) when working with RETURNS TABLE or a named composite type:

CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
RETURNS TABLE(id INTEGER, full_name TEXT, is_active BOOLEAN)
AS $$
    return [{
        "id": user_id,
        "full_name": "Jane Doe",
        "is_active": True
    }]
$$ LANGUAGE plpython3u;

Working with Arrays

PostgreSQL arrays map directly to Python lists:

CREATE OR REPLACE FUNCTION sum_array(values INTEGER[])
RETURNS INTEGER
AS $$
    return sum(values)
$$ LANGUAGE plpython3u;

SELECT sum_array(ARRAY[1, 2, 3, 4, 5]);
-- 15

Practical Example: JSON Processing

This is one of the areas where PL/Python genuinely feels more natural than PL/pgSQL, especially for deeply nested or irregular JSON structures:

CREATE OR REPLACE FUNCTION extract_json_field(data JSONB, field_path TEXT[])
RETURNS TEXT
AS $$
    import json
    obj = json.loads(data)
    for key in field_path:
        if isinstance(obj, dict) and key in obj:
            obj = obj[key]
        else:
            return None
    return str(obj)
$$ LANGUAGE plpython3u;

SELECT extract_json_field('{"user": {"address": {"city": "Lahore"}}}'::jsonb, ARRAY['user', 'address', 'city']);
-- Lahore

Writing Trigger Functions in PL/Python

Trigger functions in PL/Python receive a special TD dictionary containing the new and old row data, along with event metadata:

CREATE OR REPLACE FUNCTION normalize_email()
RETURNS trigger
AS $$
    if TD["new"]["email"] is not None:
        TD["new"]["email"] = TD["new"]["email"].strip().lower()
    return "MODIFY"
$$ LANGUAGE plpython3u;

CREATE TRIGGER trg_normalize_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION normalize_email();

Querying the Database from PL/Python

PL/Python provides plpy.execute() for running queries, along with plpy.prepare() for parameterized, reusable query plans:

CREATE OR REPLACE FUNCTION get_order_total(order_id INTEGER)
RETURNS NUMERIC
AS $$
    plan = plpy.prepare(
        "SELECT SUM(price * quantity) AS total FROM order_items WHERE order_id = $1",
        ["integer"]
    )
    result = plpy.execute(plan, [order_id])
    return result[0]["total"]
$$ LANGUAGE plpython3u;

I always use plpy.prepare() with parameter placeholders rather than building query strings with f-strings or .format(), exactly the same discipline I’d apply with psycopg2 at the application layer. Concatenating untrusted input directly into a query string opens the door to SQL injection just as easily inside the database as outside it.

Calling External Libraries and APIs

Because PL/Python is untrusted-only, it can import any Python module installed in the server’s Python environment, including third-party packages:

CREATE OR REPLACE FUNCTION compute_similarity(text_a TEXT, text_b TEXT)
RETURNS FLOAT
AS $$
    from difflib import SequenceMatcher
    return SequenceMatcher(None, text_a, text_b).ratio()
$$ LANGUAGE plpython3u;

SELECT compute_similarity('PostgreSQL', 'Postgres');

I’m intentionally cautious about making outbound network calls (like calling a REST API) from inside a database function. It’s technically possible using a library like requests, but it ties database transaction time to an external network call’s latency and failure modes, which is usually a sign the logic belongs in the application layer instead.

Common Use Cases

In real-world PostgreSQL deployments, I’ve seen PL/Python used for:

Statistical and scientific calculations using libraries like statistics, numpy, or lightweight custom math.
Complex JSON/XML transformation where nested, irregular structures are easier to walk in Python than in SQL/JSONB operators.
Text similarity and fuzzy matching for deduplication logic.
Custom data validation with more complex rules than a simple CHECK constraint can express.
Lightweight ML inference — running a small, pre-trained scikit-learn model directly against row data for scoring or classification, when latency requirements allow it.

Troubleshooting Common Issues

“permission denied for language plpython3u” error Since PL/Python is untrusted-only, only superusers can create functions with it by default. I either run the CREATE FUNCTION statement as a superuser, or explicitly GRANT USAGE ON LANGUAGE plpython3u to the specific role that needs it.

ModuleNotFoundError for a third-party package PL/Python uses the Python interpreter and installed site-packages available to the PostgreSQL server process, not necessarily the same Python environment as my application code. I install packages using the same Python interpreter PostgreSQL was built against (python3 -m pip install <package>), and I double check with SELECT plpy.execute("import sys; return sys.path")-style diagnostics, or more simply by testing the import inside a scratch function.

Function is slow on first call The first invocation of a PL/Python function in a given backend session pays a one-time interpreter and module import cost. For hot code paths, this is usually negligible after the first call, but I keep it in mind when benchmarking cold-start latency versus steady-state throughput.

Data type mismatches with NULL values Python’s None maps to SQL NULL, but subtle bugs creep in when I forget to explicitly check for None before calling string or numeric methods on a parameter that might legitimately be null. I add explicit is None checks at the top of functions that accept nullable parameters.

Unexpected transaction behavior with plpy.execute() Queries run through plpy.execute() execute within the same transaction as the calling function by default. I keep this in mind when a function performs multiple related writes — if the outer transaction rolls back, everything inside the PL/Python function rolls back with it, which is usually what I want but is worth confirming explicitly for complex multi-step logic.

Best Practices

Always use plpy.prepare() for dynamic queries. Never build SQL strings via Python string formatting with untrusted input.
Prefer generators (yield) for large SETOF results. This avoids materializing an entire large list in memory before returning it.
Be deliberate about granting USAGE on plpython3u. Since it’s untrusted, treat granting this language to a role with the same seriousness as granting broad filesystem or network access.
Avoid long-running network calls inside functions. If a function needs to talk to an external API, I strongly consider whether that logic actually belongs at the application layer instead, to avoid tying database transactions to external latency.
Pin third-party package versions. Since these are installed at the OS/Python environment level, not tracked in application requirements.txt, I document exactly what’s installed and at what version for reproducibility across environments.
Write unit tests for complex logic outside the database first. I develop and test tricky Python logic as a standalone script or module before wrapping it in a PL/Python function, which makes debugging far easier.
Keep an eye on error handling. I use plpy.error() and plpy.warning() explicitly to produce clear, actionable error messages rather than letting raw Python tracebacks bubble up unhelpfully to SQL clients.

Comparing PL/Python to Other Procedural Languages

Against PL/pgSQL, PL/Python loses ground on raw execution speed for simple, SQL-centric logic, since every call pays some interpreter and type-marshaling overhead that PL/pgSQL avoids entirely. But for anything involving statistics, JSON transformation, or general-purpose algorithmic logic, Python’s readability and library support usually make the tradeoff worthwhile.

Against PL/Perl, Python tends to win on general readability and broader team familiarity, while Perl retains an edge specifically in raw regex expressiveness. For teams without strong existing Perl experience, I find Python code easier for new team members to pick up and maintain over time.

Against PL/Java, the comparison usually comes down to organizational context — Java shops with existing business logic libraries get more value from PL/Java’s ability to directly reuse compiled Java code, while data-science-oriented teams tend to already have relevant logic prototyped in Python, making PL/Python the more natural fit.

Security Considerations

Because PL/Python is untrusted-only, this deserves particular attention compared to some of the other procedural languages covered elsewhere in this series.

No sandboxing exists. Unlike plperl or the trusted variant of PL/Java, there’s no restricted “safe” mode for PL/Python — every PL/Python function has full filesystem, network, and system access, limited only by the operating system permissions of the PostgreSQL server process itself. I treat granting USAGE on plpython3u as functionally equivalent to granting shell-level access to the database server.

Role-based access control matters more here than usual. Since there’s no language-level sandbox, the only real protection is PostgreSQL’s standard privilege system — controlling exactly who can create and execute PL/Python functions, and being conservative about extending that privilege beyond database administrators or a small, deliberately trusted set of developers.

Third-party package supply chain risk. Because PL/Python functions can import any installed Python package, a compromised or malicious dependency installed in the server’s Python environment represents a real risk vector. I treat Python packages installed for PL/Python use with the same scrutiny as production application dependencies — pinned versions, vetted sources, and periodic review.

Performance Tuning Tips

Use generators for large result sets. As shown earlier, yield-based SETOF functions avoid materializing entire result lists in memory, which matters significantly for functions processing large tables.
Minimize repeated plpy.prepare() calls. For functions called many times within a session, I prepare query plans once and reuse them rather than re-preparing on every invocation, since plpy.prepare() has its own overhead.
Avoid importing heavy libraries inside hot code paths unnecessarily. Import statements at the top of a function body are effectively cached after the first call within a session, but importing very large libraries (like certain machine learning frameworks) still adds meaningful first-call latency that I account for in cold-start-sensitive scenarios.
Profile before optimizing. I use EXPLAIN ANALYZE around queries that call PL/Python functions to see where time is actually being spent — sometimes the bottleneck is a missing index on a query inside the function, not the Python code itself.

Frequently Asked Questions

Can PL/Python functions use NumPy or pandas? Yes, as long as those packages are installed in the Python environment PostgreSQL’s plpython3u is linked against. I’ve used this pattern for lightweight numerical computations, though I’m cautious about pulling in very heavyweight dependencies purely for convenience.

Is there a trusted version of PL/Python? Older PostgreSQL versions technically had a plpythonu/plpython2u distinction with some experimentation around trusted variants, but current PostgreSQL releases only ship plpython3u, which is untrusted-only. I don’t rely on any trusted-mode sandboxing being available.

How do I return NULL from a PL/Python function? I simply return None — PL/Python automatically maps Python’s None to SQL NULL for scalar return types, and None values inside returned dictionaries or lists map to NULL fields in composite or set results as well.

Inspecting and Managing PL/Python Functions

Just like PL/Perl and PL/pgSQL functions, PL/Python functions live in the same system catalogs, so auditing them doesn’t require any special tooling:

\df+ extract_json_field

SELECT proname, prosrc, provolatile
FROM pg_proc
WHERE proname = 'extract_json_field';

I check provolatile first whenever I’m debugging unexpected caching behavior, and I read prosrc directly when I need to understand exactly what an unfamiliar PL/Python function does in a database I didn’t originally build.

DROP FUNCTION IF EXISTS extract_json_field(JSONB, TEXT[]);

Final Thoughts

PL/Python is, in my experience, the most versatile of PostgreSQL’s alternative procedural languages, largely thanks to Python’s readability and its enormous ecosystem of libraries. It’s particularly well suited to numeric, statistical, and JSON-heavy logic that would otherwise feel clunky in PL/pgSQL. The tradeoff is that it’s untrusted-only, so I have to be intentional and careful about who gets permission to create PL/Python functions in a shared environment. Used thoughtfully — with parameterized queries, careful dependency management, and clear boundaries around what belongs in the database versus the application layer — PL/Python is one of the most productive tools available for extending PostgreSQL’s procedural capabilities.