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;
$$;

SELECT grade(95) AS a, grade(85) AS b, grade(72) AS c, grade(40) AS f;

 a | b | c | f 
---+---+---+---
 A | B | C | F
(1 row)

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;
$$;

SELECT factorial(10);

 factorial 
-----------
   3628800
(1 row)

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(n2) 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;
$$;

SELECT csv_repeat('ab', 4);

 csv_repeat  
-------------
 ab,ab,ab,ab
(1 row)

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);

 order_total 
-------------
         425
(1 row)

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;
$$;

SELECT * FROM order_report(1);

 id | amount 
----+--------
  2 |    250
  1 |    100
  3 |     75
(3 rows)

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;
$$;

SELECT array_agg(x) FROM evens(1, 10) AS x;

  array_agg   
--------------
 {2,4,6,8,10}
(1 row)

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;
}
$$;

SELECT safe_divide(10, 2) AS ok, safe_divide(10, 0) AS zero;

NOTICE:  caught: division by zero
 ok | zero 
----+------
  5 |   -1
(1 row)

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;

 id | code |  label   
----+------+----------
  1 | ABC  | code:ABC
  2 | XY   | code:XY
(2 rows)

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;

 log_event 
-----------
         1
(1 row)

      note      
----------------
 deploy started
(1 row)

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}`;
$$;

SELECT describe_grp(1) AS g1, describe_grp(2) AS g2;

                 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}`;
$$;

CREATE TABLE users (id int, name text);
INSERT INTO users VALUES (1, 'Ada'), (2, 'Grace');
SELECT user_greeting(1) AS found, user_greeting(99) AS missing;

   found    |   missing   
------------+-------------
 hello, Ada | no user #99
(1 row)