plx

plx : Transpile multiple procedural dialects to PL/pgSQL

Overview

Packages

Source

pig build pkg plx;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install plx;		# install via package name, for the active PG version

pig install plx -v 18;   # install for PG 18
pig install plx -v 17;   # install for PG 17
pig install plx -v 16;   # install for PG 16
pig install plx -v 15;   # install for PG 15
pig install plx -v 14;   # install for PG 14

Create this extension with:

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'.