plxjs: the JavaScript dialect

plxjs lets you write PostgreSQL functions with JavaScript syntax (current ECMAScript). At CREATE FUNCTION time plx transpiles the body to plpgsql and stores the plpgsql in pg_proc.prosrc. The function runs on the standard plpgsql interpreter.

The language name is plxjs.

Setup

CREATE EXTENSION plx;

Function basics

Blocks are brace-delimited and statements end with ;. A scalar function ends with an explicit return.

CREATE FUNCTION add(a int, b int) RETURNS int LANGUAGE plxjs AS $$
return a + b;
$$;

Function arguments are referenced by name. Any SQL expression is valid, because expressions are passed through to plpgsql and SQL unchanged.

Local variables and types

Declare locals with let, const, or var; the keyword is removed in the generated plpgsql. plx infers the type from the first value when it is an integer, numeric, text, or boolean literal. Otherwise annotate the variable with a /*:: type */ comment before the ;.

let n = 42;             // inferred integer
let label = "count";    // inferred text
let amount /*:: numeric */;
amount = principal * rate;

The annotation text is emitted verbatim, so %TYPE and %ROWTYPE work:

let e /*:: emp%ROWTYPE */;
let s /*:: emp.sal%TYPE */;

A constant uses the const suffix in the annotation:

let pi = 3.14159 /*:: numeric const */;

Control flow

if / else if / else

if (score >= 90) { grade = "A"; }
else if (score >= 80) { grade = "B"; }
else { grade = "F"; }

switch

switch lowers to plpgsql CASE. Stacked labels become a value list. Each arm must end with break or a terminating statement (return/throw); genuine fall-through is rejected.

switch (n) {
  case 1: return "one";
  case 2: case 3: return "few";
  default: return "many";
}

Loops

Counting for and while:

for (let i = 1; i <= n; i++) {
  total = total + i;
}

while (n > 0) { n = n - 1; }

continue and break map to CONTINUE and EXIT.

Loop labels

outer: for (let i = 1; i <= n; i++) {
  for (let j = 1; j <= n; j++) {
    if (i + j >= limit) { break outer; }
  }
}

Working with data

Iterating a query

for (const row of query(`SELECT id, amount FROM orders WHERE grp = ${g}`)) {
  total = total + row.amount;
}

The row variable is a record. Field access is row.col. For bind parameters or non-literal SQL, pass extra arguments: query(sql, a) of ....

Iterating an array

let v /*:: int */;
for (const v of values) {
  total = total + v;
}

The loop variable must be annotated with its element type.

Fetching one row

let u = fetch_one(`SELECT id, name FROM users WHERE id = ${uid}`);
return u.name;

fetch_one returns all-NULL on no row. fetch_one! raises on zero or more than one row.

Running SQL

perform(`UPDATE counters SET n = n + 1 WHERE id = ${cid}`);
execute("INSERT INTO t(msg) VALUES ($1)", note);
return_query(`SELECT id FROM vip ORDER BY id`);   // set-returning function

Use bind arguments in execute for untrusted input.

Cursors

let c = open_cursor(`SELECT v FROM t ORDER BY v`);
let row = fetch_from(c);
while (found()) {
  total = total + row.v;
  row = fetch_from(c);
}
close_cursor(c);

move_cursor(c) and move_cursor(c, n) map to MOVE.

Diagnostics

let rc = row_count();   // GET DIAGNOSTICS rc = ROW_COUNT
found()                 // the FOUND variable

Set-returning functions

CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxjs AS $$
for (let i = 1; i <= n; i++) { return_next(i * i); }
return;
$$;

Errors

Throwing

throw new Error(`negative: ${v}`);

The call form raise("notice", "message") emits a RAISE at the named level (notice, warning, info, log, debug, exception).

Handling

try {
  return 100 / d;
} catch (e) {
  raise("notice", `caught: ${e.message}`);
  return -1;
} finally {
  perform("INSERT INTO log(msg) VALUES ('done')");
}

catch (e) maps to WHEN OTHERS. Accessors: e.message to SQLERRM, e.sqlstate to SQLSTATE, and e.detail, e.hint, e.constraint, e.column, e.table, e.schema, e.datatype to the matching GET STACKED DIAGNOSTICS fields.

Assertions

assert(n > 0, "must be positive");

Expressions

  • Interpolation: template literals, `total is ${amount}`. Plain "..." and '...' strings do not interpolate. Use template literals for concatenation.
  • Comparison: ==, ===, !=, !== map to = and <>. x === null becomes x IS NULL.
  • Boolean: &&, ||, ! map to AND, OR, NOT.
  • Ternary: c ? a : b becomes CASE WHEN c THEN a ELSE b END.
  • null and undefined become NULL.

Building strings in a loop

Concatenating onto a string in a loop is slow in plpgsql: s := s || 'x' is O(n2) because text is immutable and each step copies the whole string. Use += on a string variable, which plx lowers to its string builder (plx_strbuild):

let s = "" /*:: text */;
for (const row of query(`SELECT name FROM t ORDER BY id`)) {
  s += row.name;
  s += ",";
}
return s;

On PostgreSQL 18 this is amortized O(1) per append. On PostgreSQL 13 to 17 it is correct but not accelerated (the in-place optimization needs a PostgreSQL 18 feature). Note that += is treated as string append only when the variable is a string; on a numeric variable it is numeric addition.

Trigger functions

A function returning trigger can be used as a trigger. Assign to NEW fields and return NEW:

CREATE FUNCTION stamp() RETURNS trigger LANGUAGE plxjs AS $$
NEW.tag = `row ${NEW.id}`;
return NEW;
$$;

NEW, OLD, and the TG_ variables are available. Assigning to a record field (NEW.col = e) or an array element (arr[i] = e) is supported.

Semantic differences

These are intentional. plx pins semantics to SQL and plpgsql.

  • Decimal literals infer numeric, not a floating-point type. Arithmetic is exact, not IEEE 754.
  • Comparisons use SQL three-valued logic: ==, ===, !=, !== map to = and <>, so a comparison involving NULL is unknown. Only a comparison with the literal null becomes IS NULL / IS NOT NULL. A positive if/while condition treats NULL as false.
  • === and !== do not preserve JavaScript strict-type semantics; they behave like == and != and use SQL type resolution. 1 === "1" is false in JavaScript but compares as equal here. Compare like-typed values.
  • + is numeric addition; use template literals for string building.
  • Interpolating a NULL in a template literal propagates it, so the whole string is NULL. It is neither the JavaScript string "null" nor an empty string. In a message being built for throw the value falls back to an empty string instead, so one NULL cannot swallow the message.
  • Integer division and modulo follow SQL (truncate toward zero).
  • JavaScript truthiness is not emulated. A condition must be a boolean expression. A non-boolean condition is an error reported by plpgsql when the function runs.
  • Locals are function-scoped, matching JavaScript var scope.

Not supported

Rejected at CREATE FUNCTION time with a line number:

  • Function definitions, arrow functions, classes, import.
  • Object and array literals as general values.
  • for...in, and for...of over anything other than query(...) or an array.
  • switch fall-through (end each case with break or return).
  • Per-block local DECLARE (locals are function-scoped by design).

See PARITY.md for the full plpgsql construct matrix and ARCHITECTURE.md for how plx maps to the plpgsql engine.