Procedural languages

PostgreSQL ships four procedural languages in tree, each as its own extension under src/pl/. They register themselves in pg_language and provide a call handler that the executor invokes whenever a CREATE FUNCTION ... LANGUAGE foo is called.

Language	Directory	Trust level	Notes
PL/pgSQL	src/pl/plpgsql/	Trusted	Default. Most stored procedures are written in PL/pgSQL.
PL/Perl	src/pl/plperl/	Trusted (plperl) and untrusted (plperlu)	Embedded Perl.
PL/Python	src/pl/plpython/	Untrusted only (plpython3u)	Embedded CPython. The trusted variant was removed years ago.
PL/Tcl	src/pl/tcl/	Trusted (pltcl) and untrusted (pltclu)	Embedded Tcl.

"Trusted" means SQL roles can use it without superuser privileges. "Untrusted" languages can do filesystem, network, and OS operations and so are restricted.

Architecture

Each PL provides a shared library with a call handler registered as a C function. When the executor needs to call a PL function:

1. It looks up the function's pg_proc row.
2. The row's prolang points to a pg_language row.
3. The language row points to the call handler (also a pg_proc row, marked LANGUAGE C).
4. The call handler is invoked with a FunctionCallInfo representing the PL function's args.
5. The handler interprets / compiles the function body and runs it.
6. Results are returned through the standard Datum interface.

PL languages call back into SQL via the Server Programming Interface (src/backend/executor/spi.c): SPI_connect, SPI_execute, SPI_finish. This is how SELECT ... FROM foo() ends up running other SQL.

PL/pgSQL

Source: src/pl/plpgsql/src/.

PL/pgSQL is a procedural superset of SQL with control flow (IF, CASE, LOOP, WHILE, FOR), exceptions (BEGIN ... EXCEPTION WHEN ... THEN ... END), variables, cursors, triggers, and dynamic SQL (EXECUTE).

Implementation pieces:

File	Role
pl_gram.y	Bison grammar for PL/pgSQL.
pl_scanner.c / pl_funcs.c	Scanner support, helpers.
pl_comp.c	Compile a function body into a PLpgSQL_function (an AST + symbol table).
pl_exec.c	Interpret a PLpgSQL_function at runtime.
pl_handler.c	The call handler invoked by the executor.
pl_subproc_function.c	Nested sub-function support.
pl_unreserved_kwlist.h, pl_reserved_kwlist.h	Keyword tables.

A PL/pgSQL function is parsed once and cached in the function manager's plan cache. SQL inside the function (SELECT ... statements) is parsed via SPI; the parsed plan is cached too and reused on each invocation. This is what makes PL/pgSQL fast: the actual body runs in a small interpreter that mostly hands off to SPI.

pl_exec.c is the largest and most interesting file. It implements the AST walker, exception handling (using PG_TRY/PG_CATCH on top of subtransactions), PERFORM, RETURN, RETURN NEXT/RETURN QUERY, OUT parameters, cursors, etc.

Trigger functions

PL/pgSQL has special syntax for triggers — OLD, NEW, TG_OP, TG_RELNAME, etc. The handler detects whether it was called as a trigger and sets up the proper variable bindings before running the body.

Subtransactions for EXCEPTION

BEGIN
   ...
EXCEPTION WHEN unique_violation THEN
   ...
END;

Implemented by wrapping the inner block in an internal subtransaction (BeginInternalSubTransaction). On error, the subtransaction aborts and execution jumps to the EXCEPTION handler.

PL/Perl

Source: src/pl/plperl/. Embeds a Perl interpreter and provides a wrapper API (spi_exec_query, spi_query, elog, quote_literal, etc.) for running SQL from Perl code.

Two modes:

• plperl — trusted. The interpreter is sandboxed via Safe.pm; functions cannot do open, system, etc.
• plperlu — untrusted. No sandbox; superuser only.

Files:

File	Role
plperl.c	The handler and SPI wrappers.
plperl_helpers.h	Datum ↔ Perl SV conversions.
plc_perlboot.pl, plc_trusted.pl	Bootstrap Perl code.
plperl.h	Public type declarations.

PL/Perl converts SQL types to Perl types based on the Perl-style intuition: int to integers, text to strings, composite types to hash refs, arrays to array refs, boolean to undef/0/1.

PL/Python

Source: src/pl/plpython/. Embeds CPython 3 and provides a plpy module exposing SPI: plpy.execute, plpy.prepare, plpy.cursor, plpy.notice, plpy.error.

Only an untrusted variant ships (plpython3u); a trusted variant existed for Python 2 long ago but was removed because sandboxing CPython is not really possible.

Files:

• plpy_main.c — handler entry.
• plpy_exec.c — call execution.
• plpy_subxactobject.c — with plpy.subtransaction(): context manager.
• plpy_planobject.c, plpy_resultobject.c, plpy_cursorobject.c — Python wrappers around SPI objects.
• plpy_typeio.c — Datum ↔ Python conversion.

PL/Python supports composite types as Python dicts, arrays as lists, RETURNS SETOF as generators (yield ...).

PL/Tcl

Source: src/pl/tcl/. Embeds Tcl and exposes spi_exec, spi_prepare, spi_execp, elog, quote, etc., to Tcl scripts.

Two modes:

• pltcl — trusted via Tcl safe::interp.
• pltclu — untrusted.

Less commonly used in modern deployments than the others but maintained as a regular part of the project.

Building and packaging

Each PL is built only if --with-perl, --with-python, --with-tcl (or the Meson equivalent) is passed. Build looks for the language's headers via the standard config tools (perl -MExtUtils::Embed, python3-config, tclConfig.sh).

Once built, the PL is registered with CREATE EXTENSION plpgsql (auto-installed by initdb), CREATE EXTENSION plperl, etc.

Testing

Each PL has its own SQL regression tests (expected/ and sql/ under each src/pl/<name>/). They run as part of make check-world if the PL was built. Many are skipped on platforms without the prerequisite language headers.

Out-of-tree PLs

PL/Java, PL/v8 (JavaScript via V8), PL/Lua, PL/R, and others live outside this repository on PGXN or GitHub. They follow the same call-handler pattern.

Entry points for modification

• New keyword in PL/pgSQL: edit pl_unreserved_kwlist.h (or pl_reserved_kwlist.h) and the grammar in pl_gram.y.
• New built-in for PL/Python: extend the relevant plpy_* C file and update the docs.
• A whole new PL: create src/pl/<name>/ with its own grammar / runtime / handler. Substantial work; the existing PLs are templates.

For the SPI machinery the PLs all build on, see Utilities. For how pg_proc and pg_language represent functions, see Catalog.