plx

Overview

Package 2.0.1; SQL version 2.0.0.

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:

• PGXN plx 2.0.1 README
• plx 2.0.1 user guide
• plx 2.0.1 compatibility notes
• plx 2.0.1 changelog
• plx control file

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.
• Distribution 2.0.1 installs extension version 2.0.0; 2.0.1 changes PGXN packaging only and has no SQL upgrade of its own. Version 2.0.0 changes interpolation in plxruby, plxphp, plxjs, plxts, plxpython3, and plxgo: a NULL operand now makes the whole interpolated string NULL instead of silently becoming an empty string. Run ALTER EXTENSION plx UPDATE TO '2.0.0', then redeploy affected functions because existing generated PL/pgSQL in pg_proc.prosrc is not rewritten by the extension update.