Contents
Write PostgreSQL functions in the language you already know.
Ruby, PHP, JavaScript, TypeScript, Python, Go, COBOL, Oracle PL/SQL, or
Transact-SQL syntax, compiled to plpgsql at CREATE FUNCTION time.
What plx is
plx is a PostgreSQL extension that lets you write stored functions and triggers
in the dialect you already know (the current set is listed below). When you run CREATE FUNCTION,
plx transpiles the body to plpgsql and stores that plpgsql in pg_proc.prosrc.
At run time the function is executed by PostgreSQL’s own plpgsql interpreter.
There is no separate language runtime loaded into the backend, and nothing new to
run in production.
CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxruby AS $$
return "A" if score >= 90
return "B" if score >= 80
return "F"
$$;
The front end is dialect-pluggable, and the set of dialects is growing. The dialects available today are:
plxruby: a Ruby dialect. See doc/plxruby.md.plxphp: a PHP dialect. See doc/plxphp.md.plxjs: a JavaScript dialect. See doc/plxjs.md.plxpython3: a Python dialect. See doc/plxpython3.md.plxcobol: a COBOL dialect (ISO/IEC 1989:2023). See doc/plxcobol.md.plxplsql: an Oracle PL/SQL dialect. See doc/plxplsql.md.plxts: a TypeScript dialect (plxjs plus type annotations). See doc/plxts.md.plxtsql: a Transact-SQL (SQL Server) dialect. See doc/plxtsql.md.plxgo: a Go dialect. See doc/plxgo.md.
Every plpgsql statement type is reachable from every dialect. See
doc/PARITY.md for the construct matrix. The language names carry
a plx prefix, so the extension coexists with the native PL/Ruby and PL/PHP
languages in the same database.
Why it exists
PostgreSQL rewards moving logic into the database: triggers, constraints, set-returning functions, and cursors all run closest to the data. The standard way to write that logic is plpgsql. plpgsql is fast and trusted, but its syntax is unfamiliar to developers who spend their day in Ruby, PHP, JavaScript, or Python, and that unfamiliarity is often enough to keep logic in the application tier where it does not belong.
The usual alternative is an untrusted procedural language such as plpython3u or
plperlu. Those give you a familiar syntax, but at a cost: they load a full
language interpreter into the backend, most are untrusted and therefore
superuser-only, and every row they touch is marshalled across an SPI boundary
into the interpreter’s own data structures.
plx takes a different position. A new language surface does not require a new execution engine. plx changes only the syntax you write, not what runs:
- It is still plpgsql. The stored function body is plpgsql, executed by the plpgsql handler. You get plpgsql’s performance and its safety as a trusted language, with no interpreter loaded into the backend.
- Nothing is hidden. The generated plpgsql is stored in
pg_proc.prosrc, where you can read exactly what will run. plx embeds the original source as a comment so the function is idempotent to re-transpile, but the executable body is ordinary plpgsql you can inspect,pg_dump, and review. - The cost is paid once. Translation happens at
CREATE FUNCTIONtime, not per call. At run time there is no translation layer and no per-row marshalling beyond what plpgsql already does.
The goal is to meet developers where they are on syntax without changing what the database actually executes.
Who it is for
- Application developers who want to push logic into the database using syntax they already know, rather than learning plpgsql first.
- Teams standardizing on PostgreSQL who want triggers and functions written in a familiar dialect but running with plpgsql’s performance and trust model.
- Anyone who wants the generated plpgsql to be visible and reviewable rather than executed by an opaque runtime.
How it works
Each dialect provides a PlxSurface describing its keywords, block style, comment
syntax, string interpolation, and variable sigil. A shared transpiler lexes the
body, restructures statements, hoists typed DECLAREs, rewrites a fixed set of
operators and interpolations, and passes the remaining expression text through to
plpgsql and SQL unchanged. The call handler is plpgsql’s own handler, so execution
is plpgsql. See doc/ARCHITECTURE.md and
doc/TRANSPILER.md.
Example
One function, written in three dialects, each producing equivalent plpgsql:
CREATE FUNCTION grade_rb(score int) RETURNS text LANGUAGE plxruby AS $$
grade #:: text
if score >= 90
grade = "A"
elsif score >= 80
grade = "B"
else
grade = "F"
end
return grade
$$;
CREATE FUNCTION grade_php(score int) RETURNS text LANGUAGE plxphp AS $$
if ($score >= 90) { $grade = "A"; }
elseif ($score >= 80) { $grade = "B"; }
else { $grade = "F"; }
return $grade;
$$;
CREATE FUNCTION grade_js(score int) RETURNS text LANGUAGE plxjs AS $$
let grade = "F";
if (score >= 90) { grade = "A"; }
else if (score >= 80) { grade = "B"; }
else { grade = "F"; }
return grade;
$$;
The stored plpgsql (in pg_proc.prosrc) for the plxruby version is shown
below. The plxphp version is the same apart from its first line, which names
its dialect. The plxjs version differs in one more place: it initializes
grade in its DECLARE (grade text := 'F';).
/*plx:v1:plxruby:b04416a1*/
DECLARE
grade text;
BEGIN
IF score >= 90 THEN
grade := 'A';
ELSIF score >= 80 THEN
grade := 'B';
ELSE
grade := 'F';
END IF;
RETURN grade;
END;
/*plx-orig:b64$...$plx-orig*/
The first line is a sentinel naming the dialect, and the last line is a comment carrying your original source in base64, so it survives dump and restore and can be recovered (see doc/DEBUGGING.md). Everything between them is the plpgsql that runs.
Performance
Because functions execute as plpgsql, the plx dialects match plpgsql within
about 10 percent on most workloads (the worst cases measured are plxruby row
iteration at 1.17x and plxcobol’s EVALUATE branch at 1.32x) and inherit its
performance profile: several times faster than the embedded-interpreter PLs on
row iteration, fastest on call overhead, and between the embedded PLs on
arithmetic. On a pure-CPU branching loop the embedded PLs are faster (native
PL/PHP runs it in 0.40x of plpgsql’s time).
The one place plpgsql is weak is building a string in a loop: s := s || 'x' is
O(n2), because text is immutable and each step copies the whole string. plx
addresses this with a string builder (plx_strbuild, an expanded-object type with
amortized-O(1) append) and lowers the dialect append operators (s << x,
$s .= x, s += x on a string) onto it. On PostgreSQL 18 this makes in-loop
string building about 170x faster than the plpgsql idiom, and faster than the
native PLs building the same string with their own append. The in-place
optimization relies on a PostgreSQL 18 planner feature
(SupportRequestModifyInPlace); on PostgreSQL 13 to 17 the builder is correct but
not accelerated, so that case stays O(n2). Full results and method, including the
PostgreSQL 18-versus-17 scaling measurements, are in
bench/BENCHMARKS.md.
Requirements
- PostgreSQL 13 to 18.
plx is tested against PostgreSQL 13, 14, 15, 16, 17, and 18. The full regression suite passes on each. See doc/COMPATIBILITY.md.
Build and install
make PG_CONFIG=/path/to/pg_config
make install PG_CONFIG=/path/to/pg_config
Then, in a database (installing the extension requires a superuser):
CREATE EXTENSION plx;
To upgrade, install the new build and run, in each database:
ALTER EXTENSION plx UPDATE;
Upgrading changes no existing function. plx transpiles at CREATE FUNCTION
time and stores the result, so a function keeps the plpgsql it was created with
until its DDL is run again. See CHANGELOG.md for the notes on
each release.
plx cannot be moved to another schema once created, so choose the schema at
CREATE EXTENSION plx SCHEMA ... time. A function that appends to a string
(s << x, $s .= x, s += x, STRING-APPEND) stores plx’s schema name in its
generated code. If such a function is restored into a database where plx is in
a different schema (from a pg_dump -n of one schema, say), it fails when it
runs with schema "..." does not exist. Install plx in the same schema in the
target database, or run the function’s DDL again there; its original source
can be recovered from the function itself (see
DEBUGGING.md).
Documentation
- Per-dialect chapters: plxruby, plxphp, plxjs, plxpython3, plxcobol, plxplsql, plxts, plxtsql, plxgo. Each covers the full syntax, supported constructs with examples, semantic differences, and what is rejected.
- Cookbooks: verified, runnable recipes for each dialect (scalar functions, loops, string building, query loops, set-returning functions, error handling, triggers, dynamic SQL, and dialect idioms).
- doc/LIMITATIONS.md: the constraints shared by every dialect and what each individual dialect cannot do.
- doc/PARITY.md: the plpgsql construct parity matrix.
- doc/COMPATIBILITY.md: supported PostgreSQL versions.
- doc/USERGUIDE.md: examples from the PL/pgSQL manual shown in each dialect next to the plpgsql they produce.
- doc/DEBUGGING.md: correlating runtime errors to your source, and recovering the original dialect body.
- doc/ARCHITECTURE.md and doc/TRANSPILER.md: design and transpiler specification.
- bench/BENCHMARKS.md: performance across five workloads.
- CHANGELOG.md: release history.
Layout
plx.control, plx--3.0.0.sql extension control and install SQL
plx--X--Y.sql upgrade scripts between releases
Makefile PGXS build
src/ C sources (transpiler, dialect surfaces, PL handler)
doc/ per-dialect chapters, architecture, transpiler, parity
examples/ runnable recipes (triggers, SRFs, cursors, dynamic SQL)
test/ pg_regress suite, corpus runner, fuzzer
bench/ benchmark harness and results
Tests
Caution: make corpuscheck (test/run_corpus.py) drops and re-creates the
plx extension in its database, which drops every plx function there. The make
target uses contrib_regression. Run by hand, test/run_corpus.py uses the
database in PGDATABASE, so point that at a test database. It and
test/fuzz.py use the first psql on PATH (or
PLX_PSQL) and the usual libpq variables (PGHOST, PGPORT, PGDATABASE).
make installcheck # pg_regress suite
make differentialcheck # each dialect against a plpgsql reference
make corpuscheck # Ruby corpus against recorded output
make metacheck # META.json against the PGXN spec (no server)
python3 test/fuzz.py # transpiler fuzzer
make installcheck, make differentialcheck and make corpuscheck run
against an installed plx on a running server. Run differentialcheck and
corpuscheck after installcheck, which creates the contrib_regression
database they use. The make targets take psql from PG_CONFIG --bindir. The
expected output is in test/expected/. CI runs installcheck,
differentialcheck, corpuscheck, and metacheck on PostgreSQL 13 through 18.
The fuzzer needs a database where plx is installed. After installcheck, run it
with PGDATABASE=contrib_regression. It stops with an error when the plx
languages are missing, and it reports a crash, a statement cancelled at its
timeout, or an out-of-memory error as a finding.
License
MIT. See LICENSE.