PLPython

PL/Python is a procedural language extension for PostgreSQL that allows writing stored procedures and functions in the Python programming language. It embeds a Python interpreter inside the database server and provides access to PostgreSQL data and operations, enabling Python code to query and manipulate data through the server programming interface.

There are two main variants: trusted and untrusted. The untrusted variants (often named plpythonu or plpython3u) provide full Python capabilities, including operations that can access the file system or network, while the trusted variant (named plpython or plpython3) runs in a restricted sandbox to reduce security risks. The exact naming depends on the Python version in use; many installations offer plpython3u for Python 3 untrusted code.

To use PL/Python, you typically install the Python interpreter and the corresponding PostgreSQL extension, then enable the language in the database, for example by running CREATE EXTENSION plpython3u; or CREATE EXTENSION plpythonu depending on the system. Once installed, you can create functions using LANGUAGE plpython3u (or plpythonu) and write Python code that can import modules, access row data through function arguments, and execute SQL via the plpy module.

A simple example is: CREATE FUNCTION py_add(a integer, b integer) RETURNS integer AS $$ return a + b $$ LANGUAGE plpython3u; This lets the function return the sum of two integers. PL/Python supports returning scalar values or composite types, and functions can call plpy.execute to run SQL statements, enabling data-driven Python logic within PostgreSQL. Security, performance, and portability considerations guide its use, alongside alternative languages such as PL/pgSQL.