JavaScript cookbook¶
Practical recipes for the plxjs dialect. Every recipe here was run on PostgreSQL; plx transpiles the body to plpgsql and the standard interpreter executes it. See the plxjs chapter for the full language reference.
Scalar function with branching¶
Assign to a result variable through an if / else if / else chain and return it. The variable is annotated with its type because it has no initializing literal.
CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxjs AS $$
let g /*:: text */;
if (score >= 90) { g = "A"; }
else if (score >= 80) { g = "B"; }
else if (score >= 70) { g = "C"; }
else { g = "F"; }
return g;
$$;
Accumulating loop¶
A C-style for loop accumulates into a variable. The accumulator is typed bigint from its literal initializer plus the annotation.
CREATE FUNCTION factorial(n int) RETURNS bigint LANGUAGE plxjs AS $$
let result = 1 /*:: bigint */;
for (let i = 2; i <= n; i++) {
result = result * i;
}
return result;
$$;
Building a string in a loop¶
Use += on a string variable. plx lowers this to its string builder, which is amortized O(1) per append on PostgreSQL 18 instead of the O(n^2) copy that s = s || x would produce.
CREATE FUNCTION csv_repeat(word text, n int) RETURNS text LANGUAGE plxjs AS $$
let s = "" /*:: text */;
for (let i = 1; i <= n; i++) {
s += word;
if (i < n) { s += ","; }
}
return s;
$$;
Looping over a query¶
for (const r of query(...)) iterates a result set. The row is a record and columns are read as r.col. Use a template literal to interpolate the argument into the SQL text.
CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxjs AS $$
let total = 0 /*:: bigint */;
for (const r of query(`SELECT amount FROM orders WHERE grp = ${g}`)) {
total = total + r.amount;
}
return total;
$$;
Setup and call:
CREATE TABLE orders (id int, grp int, amount bigint);
INSERT INTO orders VALUES (1,1,100),(2,1,250),(3,1,75),(4,2,500);
SELECT order_total(1);
Set-returning function¶
A function declared RETURNS TABLE(...) streams rows with return_query. Qualify the column names with the table name so they are not read as the output parameters of the same name.
CREATE FUNCTION order_report(g int) RETURNS TABLE(id int, amount bigint) LANGUAGE plxjs AS $$
return_query(`SELECT orders.id, orders.amount FROM orders WHERE grp = ${g} ORDER BY orders.amount DESC`);
return;
$$;
To emit computed rows one at a time instead, use return_next with RETURNS SETOF:
CREATE FUNCTION evens(lo int, hi int) RETURNS SETOF int LANGUAGE plxjs AS $$
for (let i = lo; i <= hi; i++) {
if (i % 2 == 0) { return_next(i); }
}
return;
$$;
Error handling with try / catch¶
try / catch (e) maps to a plpgsql exception block (WHEN OTHERS). e.message reads SQLERRM. The raise call form emits a RAISE at the named level.
CREATE FUNCTION safe_divide(a int, b int) RETURNS int LANGUAGE plxjs AS $$
try {
return a / b;
} catch (e) {
raise("notice", `caught: ${e.message}`);
return -1;
}
$$;
Trigger function¶
A function returning trigger can be attached to a table. Assign to NEW fields and return NEW. This one normalizes a code and derives a label before the row is written, on both insert and update.
CREATE FUNCTION normalize_product() RETURNS trigger LANGUAGE plxjs AS $$
NEW.code = upper(NEW.code);
NEW.label = `code:${NEW.code}`;
return NEW;
$$;
CREATE TABLE products (id int, code text, label text);
CREATE TRIGGER trg_norm BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION normalize_product();
INSERT INTO products(id, code) VALUES (1, 'abc'), (2, 'xy');
SELECT id, code, label FROM products ORDER BY id;
Dynamic SQL with bind parameters¶
Use execute(sql, arg, ...) and reference the arguments as $1, $2, and so on. Bind parameters keep untrusted values out of the SQL text, which avoids injection. row_count() reads how many rows the statement affected.
CREATE FUNCTION log_event(msg text) RETURNS bigint LANGUAGE plxjs AS $$
execute("INSERT INTO events(note) VALUES ($1)", msg);
let n = row_count() /*:: bigint */;
return n;
$$;
CREATE TABLE events (id serial PRIMARY KEY, note text);
SELECT log_event('deploy started');
SELECT note FROM events ORDER BY id;
JS idioms: template literals, for...of, and switch¶
This function combines the common JavaScript idioms plx supports: a for...of loop over a query, a switch that lowers to CASE, and a template literal that interpolates several values into the return string.
CREATE FUNCTION describe_grp(g int) RETURNS text LANGUAGE plxjs AS $$
let count = 0 /*:: int */;
let total = 0 /*:: bigint */;
for (const r of query(`SELECT amount FROM orders WHERE grp = ${g}`)) {
count = count + 1;
total = total + r.amount;
}
let size /*:: text */;
switch (count) {
case 0: size = "empty"; break;
case 1: size = "single"; break;
default: size = "many"; break;
}
return `group ${g}: ${count} orders (${size}), total ${total}`;
$$;
g1 | g2
-------------------------------------+---------------------------------------
group 1: 3 orders (many), total 425 | group 2: 1 orders (single), total 500
(1 row)
Single-row lookup with a ternary¶
fetch_one returns one row, or an all-NULL record when nothing matches. A ternary (c ? a : b, which lowers to CASE) plus a === null test turns the miss into a readable message. x === null becomes IS NULL.
CREATE FUNCTION user_greeting(uid int) RETURNS text LANGUAGE plxjs AS $$
let u = fetch_one(`SELECT name FROM users WHERE id = ${uid}`);
return u.name === null ? `no user #${uid}` : `hello, ${u.name}`;
$$;