PL/Python (plpython3u)

What PL/Python does

PL/Python embeds a Python interpreter inside the PostgreSQL server process. When you create a function with LANGUAGE plpython3u, the function body is ordinary Python code. PostgreSQL handles the translation between SQL types and Python types automatically — integers become Python ints, text becomes strings, arrays become lists, and composite types become dictionaries.

The embedded interpreter has access to the plpy module, which provides the Server Programming Interface (SPI). Through SPI, Python functions can execute SQL queries, use prepared statements with parameters, iterate over results with cursors, and raise PostgreSQL errors and notices. This means a single PL/Python function can combine Python logic with database operations in one atomic unit.

Because PL/Python is untrusted, the Python code runs with the same OS permissions as the PostgreSQL server process. It can read files, open network connections, import any installed library, and interact with the operating system. This power is exactly why only superusers can create PL/Python functions — there is no sandbox restricting what the code can do. I appreciate that PostgreSQL is forthright about this. The "u" in the name is not buried in a footnote; it is right there in every CREATE FUNCTION call, a small but honest reminder of the arrangement you have entered into.

When to use PL/Python

PL/Python is the right choice when you need capabilities that PL/pgSQL cannot provide:

• Complex string processing — Python's string handling, regular expressions, and text parsing libraries are far more capable than PL/pgSQL's string functions
• Calling external APIs — use requests or urllib to call REST APIs, webhooks, or external services from within a database function
• Data transformation — leverage Python libraries like json, csv, xml.etree, or pandas for complex data munging that would be painful in SQL
• ML inference inside the database — load a trained model and run predictions without moving data out of PostgreSQL
• Custom aggregation logic — when SQL window functions and GROUP BY are not expressive enough for the computation you need
• Integration with Python ecosystems — access scientific computing (NumPy, SciPy), natural language processing, or any domain-specific Python library

If your function only needs to run SQL queries and do simple control flow, PL/pgSQL is a better fit — it is trusted, available everywhere, and has lower overhead. One does not hire a specialist when the regular staff are perfectly suited to the task. Reach for PL/Python when you specifically need Python's language features or library ecosystem.

Installation and setup

PL/Python is bundled with PostgreSQL but is not installed by default. The PostgreSQL server must be compiled with Python support, and the Python 3 shared library (libpython3.so on Linux) must be available on the server at runtime.

-- Install the PL/Python extension (requires superuser)
CREATE EXTENSION plpython3u;

-- Verify it's installed
SELECT * FROM pg_extension WHERE extname = 'plpython3u';

On most Linux distributions, you install the PL/Python package separately from the core PostgreSQL server. For example, on Debian/Ubuntu: apt install postgresql-plpython3-16 (adjust the version number). On Red Hat/CentOS: dnf install postgresql16-plpython3.

Only superusers can run CREATE EXTENSION plpython3u and create functions in this language. Regular users can be granted EXECUTE permission on specific PL/Python functions, but they cannot create new ones.

Writing functions

Basic function

A PL/Python function body is standard Python code inside a $$ delimiter. The function arguments are available as Python variables, and the return value is automatically converted to the declared SQL return type.

-- A simple function that reverses a string
CREATE FUNCTION reverse_string(input text)
RETURNS text
AS $$
  return input[::-1]
$$ LANGUAGE plpython3u;

SELECT reverse_string('PostgreSQL');
-- Returns: LQSergtsoP

Python inside $$ delimiters, returning a SQL type. The ceremony is minimal.

Database access with plpy

The plpy module is automatically available in every PL/Python function. Use plpy.execute() for simple queries, plpy.prepare() for parameterized statements, and plpy.cursor() to iterate over large result sets without loading them entirely into memory.

-- Query the database from inside a Python function using plpy
CREATE FUNCTION get_active_user_count()
RETURNS integer
AS $$
  result = plpy.execute("SELECT count(*) AS cnt FROM users WHERE active = true")
  return result[0]['cnt']
$$ LANGUAGE plpython3u;

-- Using prepared statements with parameters
CREATE FUNCTION get_user_email(user_id integer)
RETURNS text
AS $$
  plan = plpy.prepare("SELECT email FROM users WHERE id = $1", ["integer"])
  result = plpy.execute(plan, [user_id])
  if result.nrows() > 0:
      return result[0]['email']
  return None
$$ LANGUAGE plpython3u;

Importing Python libraries

You can import any Python module that is installed in the Python environment PostgreSQL is linked against. Imports are cached for the duration of the database session.

-- Use any Python library installed on the server
CREATE FUNCTION extract_domain(url text)
RETURNS text
AS $$
  from urllib.parse import urlparse
  parsed = urlparse(url)
  return parsed.netloc
$$ LANGUAGE plpython3u;

SELECT extract_domain('https://www.example.com/path?q=1');
-- Returns: www.example.com

Processing large result sets

For queries that return many rows, use plpy.cursor() to fetch results in batches rather than loading everything into memory at once.

-- Process large result sets efficiently with cursors
CREATE FUNCTION process_large_table()
RETURNS integer
AS $$
  cursor = plpy.cursor("SELECT id, data FROM large_table")
  processed = 0

  while True:
      rows = cursor.fetch(1000)
      if not rows:
          break
      for row in rows:
          # Process each row
          processed += 1

  return processed
$$ LANGUAGE plpython3u;

Trigger support

PL/Python can be used to write trigger functions. Inside a trigger, the special TD dictionary provides context about the triggering event: TD["event"] contains the operation type (INSERT, UPDATE, DELETE), TD["old"] and TD["new"] contain the old and new row values as dictionaries, and TD["table_name"] identifies the table.

-- PL/Python trigger that logs row changes
CREATE FUNCTION audit_trigger()
RETURNS trigger
AS $$
  import json
  from datetime import datetime

  old = TD.get("old")
  new = TD.get("new")
  event = TD["event"]
  table = TD["table_name"]

  plpy.execute(
      plpy.prepare(
          "INSERT INTO audit_log (table_name, event, old_data, new_data, changed_at) "
          "VALUES ($1, $2, $3, $4, $5)",
          ["text", "text", "jsonb", "jsonb", "timestamp"]
      ),
      [table, event, json.dumps(old) if old else None,
       json.dumps(new) if new else None, datetime.now().isoformat()]
  )

  return "OK"
$$ LANGUAGE plpython3u;

CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

A complete audit trail — timestamp, event, old and new values — assembled in Python and written through SPI. For BEFORE triggers, the function can return "SKIP" to suppress the operation or "MODIFY" after changing values in TD["new"] to alter the row being inserted or updated.

Cloud availability

Because PL/Python is an untrusted language that can access the server's filesystem and network, most managed PostgreSQL providers do not support it.

The table above is, I should note, almost entirely a column of refusals. If you need PL/Python in production, self-managed PostgreSQL (on bare metal, VMs, or containers) or Crunchy Bridge are your primary options. For managed services that restrict untrusted languages, consider using Trusted Language Extensions (TLE) or moving Python logic to the application layer.

How Gold Lapel relates

Allow me to be direct about what I can and cannot see here. Gold Lapel operates at the SQL query level — intercepting queries between your application and PostgreSQL, analyzing patterns, and applying optimizations like materialized views and index recommendations. PL/Python functions are, to me, opaque. When a function executes SQL internally via plpy.execute(), those queries travel through PostgreSQL's SPI, never passing through the proxy.

I can optimize the SQL that calls your PL/Python functions — SELECT my_python_func(col) FROM table is perfectly visible. But the SQL running inside those functions is beyond my reach. If a PL/Python function contains performance-critical queries, those must be optimized by hand: proper indexing, sensible query structure, prepared statement reuse within the function itself.

I should be forthcoming: this is rarely a limitation in practice. PL/Python functions are typically employed for computation and transformation rather than complex querying, and the SQL they execute internally tends to be simple lookups or inserts. But a butler who pretends to see behind closed doors is no butler at all.