Contents

• pg_pure — write PostgreSQL functions in Pure
  • Two languages: pg_pure and pg_pureu
  • Examples
  • Requirements
    • Building libpure (once)
    • PostgreSQL JIT coexistence
  • Build and install pg_pure
  • Pure behaviour that surprises SQL users
  • Performance
  • Status
  • Documentation
  • License

pg_pure — write PostgreSQL functions in Pure

pg_pure makes Pure a procedural language in PostgreSQL, the way PL/Perl and PL/Python do for their languages. A function body is Pure code; it runs in a Pure interpreter (libpure) embedded in the backend, and from there it can reach the database through SPI.

Pure is a functional language built on term rewriting: equational definitions with pattern matching, exact rational and arbitrary-precision arithmetic, lazy streams, and genuine symbolic computation. That last part is the reason to reach for it in SQL. No other in-database language simplifies x*1 + 0*y to x.

CREATE EXTENSION pg_pureu;

CREATE FUNCTION simplify(e text) RETURNS text LANGUAGE pg_pureu AS $$
  str (reduce (val e) with
         x*1 = x; 1*x = x; 0*x = 0; x*0 = 0; x+0 = x; 0+x = x
       end)
$$;

SELECT simplify('x*1 + 0*y + (x+0)');   -- x+x

Two languages: pg_pure and pg_pureu

Like PL/Perl (plperl/plperlu) and PL/Tcl (pltcl/pltclu), pg_pure ships a trusted and an untrusted variant from one shared library:

Extension	Trust	Who can use it	Status
pg_pure	trusted	any user	works; runs in a libpure sandbox
pg_pureu	untrusted	superuser only	works

A Pure program can normally call any C function through an extern declaration, so untrusted Pure is as powerful as a C extension; only a superuser may create pg_pureu functions. The trusted pg_pure runs each function in a libpure safe-mode interpreter: a body that tries extern, inline C, eval/evalcmd, a native or non-allowlisted using, or a dangerous prelude function (exit, malloc, raw pointers) is refused as an SQL ERROR, and the backend is unharmed. So an unprivileged user can write pg_pure functions safely, the way PL/Perl’s plperl is safe. See DESIGN.md §9 for exactly what the sandbox blocks and what is still to come (per-SQL-user interpreters, a curated prelude, a security review before multi-tenant reliance).

Examples

All of these run under either language; pg_pure (trusted) is usable by any user. Every snippet here is exercised by the regression suite or was run on the build host.

Arbitrary-precision arithmetic, because Pure integers promote to bigints:

CREATE FUNCTION factorial(n int) RETURNS numeric LANGUAGE pg_pureu AS $$
  foldl (*) 1L (1..n)
$$;
SELECT factorial(30);   -- 265252859812191058636308480000000

Exact rationals (the math module, loaded by default, gives %):

CREATE FUNCTION harmonic(n int) RETURNS numeric LANGUAGE pg_pureu AS $$
  using math;
  foldl (+) 0 [1%k | k = 1..n]
$$;
SELECT harmonic(10);    -- 7381/2520 as an exact numeric: 2.9289682539...

Recursion and pattern matching:

CREATE FUNCTION fib(n int) RETURNS numeric LANGUAGE pg_pureu AS $$
  loop n 0L 1L with loop 0 a _ = a; loop k a b = loop (k-1) b (a+b) end
$$;
SELECT fib(100);        -- 354224848179261915075

A set-returning function from a lazy stream (an infinite prime sieve, cut to n):

CREATE FUNCTION primes(n int) RETURNS SETOF int LANGUAGE pg_pureu AS $$
  take n (sieve (2..inf)) with
    sieve (p:qs) = p : sieve [q | q = qs; q mod p] &
  end
$$;
SELECT * FROM primes(10);   -- 2 3 5 7 11 13 17 19 23 29

The body must bound the stream itself (take n ...): a set-returning function materializes its rows before LIMIT applies, so SELECT * FROM primes(10) LIMIT 4 still computes ten primes, and an unbounded body is stopped by cancel or a row cap, not by LIMIT.

Numeric arrays: float8[] maps to a Pure double matrix, so vector and matrix math works both directions. A 1-D array is a row vector, a 2-D array an n-by-m matrix:

CREATE FUNCTION vscale(v float8[], k float8) RETURNS float8[]
  LANGUAGE pg_pure AS $$ map (*k) v $$;
SELECT vscale(ARRAY[1,2,3]::float8[], 10.0);      -- {10,20,30}

CREATE FUNCTION mtransp(m float8[]) RETURNS float8[]
  LANGUAGE pg_pure AS $$ using gsl::matrix; transpose m $$;
SELECT mtransp(ARRAY[[1,2,3],[4,5,6]]::float8[]);  -- {{1,4},{2,5},{3,6}}

For a dense linear solve, use the compiled routine, not a hand-written one: a pure-Pure Cholesky is correct but slow, so prefer gsl::linalg::cholesky_solve (in pure-gsl as of the module version pg_pure 0.4.0 builds against). It solves a symmetric positive-definite A x = b over float8[] at C speed:

-- the DBA allow-lists the module once: pg_pure.using = 'math gsl::linalg'
CREATE FUNCTION solve_spd(a float8[], b float8[]) RETURNS float8[]
  LANGUAGE pg_pure AS $$ gsl::linalg::cholesky_solve a b $$;

That runs about 40x faster than the same solve in PL/pgSQL. See the pg.ddx.io evaluation notes for the measured trade-off.

Reaching the database with SPI, with a parameter passed as data (never spliced), and catching a SQL error by its SQLSTATE:

CREATE FUNCTION add_user(email text) RETURNS bool LANGUAGE pg_pureu AS $$
  catch dup (sql::exec ("INSERT INTO users(email) VALUES ($1)", [email]) > 0)
  with dup (sql::error "23505" _) = false; dup e = throw e end
$$;

Parameters go to the planner as Datums via SPI_execute_with_args: the call shape is sql::query (q, [p1, p2, ...]) and sql::exec (q, [...]), and a parameter’s SQL type is inferred from the Pure value (int, double, string, bigint, or sql::null). The no-parameter forms sql::query q and sql::exec q still work.

Requirements

• PostgreSQL 17, 18, or 19. All three build and pass the regression suite (gated in CI; 19 against the PGDG snapshot until it is released).
• libpure, built from the fork below. It is not packaged; you build it once.
• A C toolchain and the server headers (postgresql-server-dev-NN).

Building libpure (once)

pg_pure embeds libpure from a fork that makes the runtime safe to embed in a host process (it does not call exit(), returns exceptions to the host, and offers hooks for query-cancel and the stack limit). Build and install it from:

git clone --branch libpure-embeddable https://github.com/gburd/pure-lang.git
cd pure-lang/pure
autoreconf -fi
./configure --prefix=$HOME/pure --with-tool-prefix=$(llvm-config --bindir) --enable-release
make -j
make install          # installs libpure.so + headers + the prelude under $HOME/pure

libpure needs LLVM (20.1+; tested with 21) and GMP/MPFR/readline. On Debian: apt install llvm-21-dev clang-21 libgmp-dev libmpfr-dev libreadline-dev.

PostgreSQL JIT coexistence

libpure JIT-compiles Pure with LLVM, and so does PostgreSQL’s own JIT (llvmjit), often a different LLVM major. The fork’s libpure hides its LLVM symbols so the two cannot clash, so nothing special is required: pg_pure runs on a normal jit = on server. (This is tested in CI with jit_above_cost = 0, which forces a JIT-compiled query into the same backend as a Pure function, on PostgreSQL 17/18/19 with zero crashes.) DESIGN.md §5 has the history and the one guard the test suite keeps against a future libpure that regresses it.

Build and install pg_pure

make PG_CONFIG=/path/to/pg_config PURE_PREFIX=$HOME/pure
sudo make install PG_CONFIG=/path/to/pg_config PURE_PREFIX=$HOME/pure

PURE_PREFIX is where you installed libpure above. The prelude is found through libpure’s built-in path; if the server runs with a non-standard install, set PURELIB in the server’s environment to the directory holding prelude.pure.

Then, in a database, as a superuser:

CREATE EXTENSION pg_pureu;

For low per-call latency, preload the interpreter in the postmaster so backends inherit it (otherwise the first call in each new backend pays a one-time cost, see Performance):

# postgresql.conf
shared_preload_libraries = 'pg_pure'
pg_pure.using = 'math'      # modules every interpreter loads; default: math

Pure behaviour that surprises SQL users

These are real rough edges, not bugs. Each is something a SQL author hits and needs to understand.

• An unmatched application is not an error — it is a normal form. If your rules do not match the argument, Pure leaves the expression unevaluated (f 2 stays f 2) rather than failing. pg_pure cannot convert that to the return type, so you get: ERROR: pg_pureu: result f 2 cannot be converted to integer HINT: Check the argument types your rules expect; an unmatched application stays unevaluated in Pure. The fix is almost always a catch-all rule (f _ = ...) or matching the right type.

• SQL int/smallint are Pure machine integers, and they wrap silently. 2147483647 + 1 is -2147483648 inside Pure, as the Pure manual documents. The final result is range-checked when it is converted back (an out-of-range smallint is an error, not a wrong number), but a wrap inside the computation is not caught. If you need big intermediate values, work in int8/numeric (Pure bigints) or convert: bigint x.

• SQL bigint arrives as a Pure bigint, and 0L does not match the pattern 0. A rule f 0 = ... will not fire for an int8 argument, and take 5L xs does not reduce (take wants a machine int). For int8 arguments compare with ==, or convert with int n when you know the value fits.

• numeric is exact in and out. An integral numeric is a Pure bigint; a fractional one is an exact rational (needs math, loaded by default). A non-terminating rational like 1%3 returned as numeric is rounded to the display scale, as any numeric division is. To return a rational as a float, convert in Pure: double (1%3).

• A text result must be a Pure string. pg_pure will not stringify an arbitrary Pure value for you (that would hide the unmatched-application error above as plausible-but-wrong text). Call str yourself if you mean to.

• Exceptions. An uncaught Pure throw becomes ERROR: pg_pureu: uncaught exception: <value> (SQLSTATE 38000) and the session keeps working. A SQL error raised inside sql::exec/sql::query reaches Pure as sql::error "SQLSTATE" "message"; catch it to continue the transaction, or let it propagate to re-raise the original SQL error intact.

• Runaway recursion is caught. Pure stack overflow becomes the exception stack_fault (bounded by max_stack_depth), then a SQL error — not a crash.

• using goes at the top of the body. Module loads must precede the expression: using gsl; gsl::stats::sd (matrix xs), not inline.

• Global definitions made with eval are not transactional. They survive a ROLLBACK. Ordinary function bodies have no such effect; a stray ; in a body is a compile error, not a global leak.

• A statement_timeout or pg_terminate_backend cancels a running Pure computation, including an infinite loop, through libpure’s interrupt hook.

Performance

Measured on an EC2 c7i.4xlarge, Debian 13, PG 18, libpure on LLVM 21:

First pg_pureu call in a fresh backend (no preload)	~5.3 s (interpreter create + LLVM codegen of the prelude)
First call, preloaded and warmed in the postmaster	~0.16 s
Each further new function’s first call	72–165 ms
1M calls of x*y+1 in one SELECT	plain SQL ~97 ms, PL/pgSQL ~306 ms, pg_pureu ~455 ms
Backend memory after the first call	~105 MB private

The one-time first-call cost is LLVM compiling the Pure prelude code the call reaches. Preload (shared_preload_libraries) pays it once in the postmaster; backends then inherit it by fork. A connection pooler helps either way, since the interpreter is per-backend.

Status

0.6, in development. Working: both languages (pg_pure trusted via libpure safe mode, pg_pureu untrusted), scalar functions over int2/int4/int8/ float4/float8/numeric/bool/text, exact numeric, NULL as sql::null, float8[] <-> Pure double matrix, sql::query/sql::exec (with parameters passed as Datums), set-returning functions (RETURNS SETOF of a scalar or array, from a Pure list or a body-bounded lazy stream), sql::notice/ sql::warning, SQL-error-as-sql::error, DO blocks, the validator, preload warm-up, the add-on Pure modules (using gsl, …), and a compiled gsl::linalg::cholesky_solve SPD solve. A Nix package builds libpure and the modules; a TAP suite covers the JIT+Pure crash matrix, cancel, concurrency and bounded memory. PostgreSQL 17/18/19 pass both regression suites with jit=on and zero crashes.

Not yet: RETURNS TABLE / SETOF composite, triggers, composite types, per-SQL-user trusted interpreters, and a curated safe-mode prelude. See ROADMAP.md.

Documentation

• DESIGN.md — the full design, the measurements, and the LLVM coexistence analysis.
• ROADMAP.md — the phased plan and what is done.

License

PostgreSQL license (see LICENSE). pg_pure links libpure (LGPL-3) dynamically. Pure library modules you load (gsl, glpk are GPL; mpfr, csv, stllib are LGPL/MIT) are loaded by you at runtime, not shipped with pg_pure. This is not legal advice.