plx

Overview

Uses PostgreSQL’s built-in PL/pgSQL call handler; no control-file dependency is declared.

Version

Build

You can build the RPM / DEB packages for plx using pig build:

pig build pkg plx         # build RPM / DEB packages

Install

You can install plx directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install plx;          # Install for current active PG version

pig ext install -y plx -v 18  # PG 18
pig ext install -y plx -v 17  # PG 17
pig ext install -y plx -v 16  # PG 16
pig ext install -y plx -v 15  # PG 15
pig ext install -y plx -v 14  # PG 14

dnf install -y plx_18       # PG 18
dnf install -y plx_17       # PG 17
dnf install -y plx_16       # PG 16
dnf install -y plx_15       # PG 15
dnf install -y plx_14       # PG 14

apt install -y postgresql-18-plx   # PG 18
apt install -y postgresql-17-plx   # PG 17
apt install -y postgresql-16-plx   # PG 16
apt install -y postgresql-15-plx   # PG 15
apt install -y postgresql-14-plx   # PG 14

Create Extension:

CREATE EXTENSION plx;

Usage

Sources:

• plx 1.3.1 README
• plx documentation
• plx user guide
• plx limitations
• plx 1.3.1 release

plx provides familiar procedural-language dialects that transpile to ordinary PL/pgSQL when CREATE FUNCTION runs. PostgreSQL stores and executes the generated PL/pgSQL with its built-in trusted handler; no Ruby, PHP, JavaScript, Python, Go, COBOL, Oracle, or SQL Server runtime is loaded into the backend.

CREATE EXTENSION plx;

Available Dialects

All dialects target the same PL/pgSQL statement surface, including assignments, conditionals, loops, query iteration, dynamic SQL, cursors, exceptions, triggers, and set-returning functions.

Create a Function

Choose a dialect in the LANGUAGE clause while keeping the function signature in PostgreSQL types:

CREATE FUNCTION grade(score integer)
RETURNS text
LANGUAGE plxruby
AS $$
  grade #:: text
  if score >= 90
    grade = "A"
  elsif score >= 80
    grade = "B"
  else
    grade = "F"
  end
  return grade
$$;

SELECT grade(85);

Translation happens once, when the function is created. The executable body stored in pg_proc.prosrc is regular PL/pgSQL, so it can be dumped, reviewed, and run without a separate interpreter.

Inspect and Debug Generated Code

SELECT pg_get_functiondef('grade(integer)'::regprocedure);
SELECT prosrc
FROM pg_proc
WHERE oid = 'grade(integer)'::regprocedure;

SELECT plx_source('grade(integer)'::regprocedure);

Runtime error line numbers refer to generated PL/pgSQL. plx_source() recovers the original embedded dialect body; use it together with pg_get_functiondef() when correlating an error with the source.

SQL and String Building

Expressions retain PostgreSQL SQL semantics rather than emulating a complete source-language runtime. Use each dialect’s query/execute form for SQL and explicit PostgreSQL types for non-literal expressions. The plx_strbuild expanded-object helper accelerates repeated string appends on PostgreSQL 18:

CREATE FUNCTION labels(n integer)
RETURNS text
LANGUAGE plxjs
AS $$
  let out: text = "";
  for (let i = 1; i <= n; i++) {
    out += `item-${i},`;
  }
  return out;
$$;

The builder remains correct on PostgreSQL 13-17, but its in-place optimization requires PostgreSQL 18.

Boundaries and Caveats

• plx implements syntax surfaces, not the source languages’ runtimes: there are no gems, Python modules, JavaScript imports, Go goroutines, PHP classes, Oracle packages, or SQL Server transaction commands.
• Functions run in PL/pgSQL’s trusted sandbox, with no direct filesystem, network, arbitrary native-code, or transaction-control access.
• Parameters and return types must be PostgreSQL types. Type inference for locals is limited; explicitly declare types for calls and compound expressions.
• SQL uses three-valued logic and PostgreSQL numeric/string semantics. Source-language truthiness and string concatenation with + are not reproduced.
• Locals are hoisted into one PL/pgSQL DECLARE block, so block-local scope and redeclaration with a different type are unavailable.
• Version 1.3.1 is a code-only safety release: it adds lexer/string-builder capacity guards, stack-depth checks, bounded indentation handling, and fixes for raw-string, PHP interpolation, and non-decimal integer literal parsing. After installing the binary, run ALTER EXTENSION plx UPDATE TO '1.3.1'.