Lua cookbook

Practical recipes for the plxlua dialect. Every recipe here was run on PostgreSQL; plx transpiles the body to plpgsql and the standard interpreter executes it. See the plxlua chapter for the full language reference.

Scalar function with branching

Return a value from an if / elseif / else chain. Each branch returns directly, so no result variable is needed.

CREATE FUNCTION letter_grade(score int) RETURNS text LANGUAGE plxlua AS $$
if score >= 90 then
  return "A"
elseif score >= 80 then
  return "B"
elseif score >= 70 then
  return "C"
elseif score >= 60 then
  return "D"
else
  return "F"
end
$$;
SELECT letter_grade(95), letter_grade(83), letter_grade(50);
 letter_grade | letter_grade | letter_grade 
--------------+--------------+--------------
 A            | B            | F

Accumulating loop

A numeric for loop. The accumulator is annotated bigint with a --:: comment so the product does not overflow int.

CREATE FUNCTION factorial(n int) RETURNS bigint LANGUAGE plxlua AS $$
local result = 1 --:: bigint
for i = 1, n do
  result = result * i
end
return result
$$;
SELECT factorial(5), factorial(10);
 factorial | factorial 
-----------+-----------
       120 |   3628800

Building a string in a loop

s = s .. x on a string local is lowered to plx’s string builder, so repeated appends are amortized O(1) on PostgreSQL 18 rather than the O(n2) of s := s || x.

CREATE FUNCTION repeat_stars(n int) RETURNS text LANGUAGE plxlua AS $$
local s = ""
for i = 1, n do
  s = s .. "*"
end
return s
$$;
SELECT repeat_stars(5);
 repeat_stars 
--------------
 *****

Looping over a query result

for r in query(...) do iterates the rows. Lua has no string interpolation, so the group is passed as a bind argument ($1), and fields are read with r.col.

CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxlua AS $$
local total = 0 --:: bigint
for r in query("SELECT amount FROM ck_orders WHERE grp = $1", g) do
  total = total + r.amount
end
return total
$$;
CREATE TABLE ck_orders(id int, amount int, grp int);
INSERT INTO ck_orders VALUES (1, 100, 7), (2, 250, 7), (3, 999, 8);
SELECT order_total(7);
 order_total 
-------------
         350

A set-returning function

RETURNS SETOF int with emit(v) produces one row per value. A bare return ends the function.

CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxlua AS $$
for i = 1, n do
  emit(i * i)
end
return
$$;
SELECT * FROM squares(5);
 squares 
---------
       1
       4
       9
      16
      25

Rows with several columns

In a RETURNS TABLE function, emit(a, b) assigns the output columns in order and produces the row.

CREATE FUNCTION countdown(n int) RETURNS TABLE(step int, label text) LANGUAGE plxlua AS $$
for i = n, 1, -1 do
  emit(i, i == 1 and "liftoff" or "t-minus " .. i)
end
$$;
SELECT * FROM countdown(3);
 step |   label   
------+-----------
    3 | t-minus 3
    2 | t-minus 2
    1 | liftoff

Trapping an error

pcall(function() ... end) maps to a plpgsql exception block. ok is false when the protected function raised, and err holds the error message.

CREATE FUNCTION safe_divide(a int, b int) RETURNS int LANGUAGE plxlua AS $$
local result --:: int
local ok, err = pcall(function()
  result = a // b
end)
if not ok then
  print("caught: " .. err)
  return -1
end
return result
$$;
SELECT safe_divide(10, 2), safe_divide(10, 0);
NOTICE:  caught: division by zero
 safe_divide | safe_divide 
-------------+-------------
           5 |          -1

Handling one error condition

Lua has no exception classes. Test err.sqlstate to handle one condition, and raise anything else again with error(err), which keeps its SQLSTATE, message, detail and hint.

CREATE FUNCTION add_tag(t text) RETURNS text LANGUAGE plxlua AS $$
local ok, err = pcall(function()
  execute("INSERT INTO ck_tags VALUES ($1)", t)
end)
if ok then return "added " .. t end
if err.sqlstate == "23505" then
  return "already have " .. t .. " (" .. err.constraint .. ")"
end
error(err)
$$;
CREATE TABLE ck_tags(tag text PRIMARY KEY);
SELECT add_tag('red'), add_tag('red');
  add_tag  |             add_tag             
-----------+---------------------------------
 added red | already have red (ck_tags_pkey)

Raising an error with a SQLSTATE

error{...} sets the message, SQLSTATE, detail and hint of the error, where error(msg) sets only the message.

CREATE FUNCTION check_qty(q int) RETURNS int LANGUAGE plxlua AS $$
if q < 0 then
  error{message = "quantity cannot be negative", sqlstate = "22023",
        detail = "got " .. q, hint = "pass zero or more"}
end
return q
$$;
SELECT check_qty(-3);
ERROR:  quantity cannot be negative
DETAIL:  got -3
HINT:  pass zero or more
CONTEXT:  PL/pgSQL function check_qty(integer) line 4 at RAISE

A trigger function

A function returning trigger can assign to NEW fields and return NEW. TG_OP tells an insert from an update.

CREATE FUNCTION stamp_change() RETURNS trigger LANGUAGE plxlua AS $$
if TG_OP == "INSERT" then
  NEW.changed_at = "row " .. NEW.id .. " created"
else
  NEW.changed_at = "row " .. NEW.id .. " touched"
end
return NEW
$$;
CREATE TABLE ck_audit(id int primary key, note text, changed_at text);
CREATE TRIGGER ck_audit_bi BEFORE INSERT OR UPDATE ON ck_audit
  FOR EACH ROW EXECUTE FUNCTION stamp_change();
INSERT INTO ck_audit(id, note) VALUES (1, 'first'), (2, 'second');
UPDATE ck_audit SET note = 'edited' WHERE id = 1;
SELECT * FROM ck_audit ORDER BY id;
 id |  note  |  changed_at   
----+--------+---------------
  1 | edited | row 1 touched
  2 | second | row 2 created

Dynamic SQL with bind parameters

Pass values as extra arguments so they are sent as bind parameters ($1, $2, …). Build identifiers into the SQL text with .. and quote_ident(). fetch_one returns a single record.

CREATE FUNCTION count_where(tbl text, minid int) RETURNS bigint LANGUAGE plxlua AS $$
local r = fetch_one("SELECT count(*) AS c FROM " .. quote_ident(tbl) .. " WHERE id >= $1", minid)
return r.c
$$;
CREATE TABLE ck_users(id int, name text);
INSERT INTO ck_users VALUES (1, 'Ada'), (2, 'Alan'), (3, 'Grace');
SELECT count_where('ck_users', 2);
 count_where 
-------------
           2

Lua idioms: or, and/or, continue

x or default lowers to COALESCE, c and a or b to CASE, and goto continue with ::continue:: at the end of the loop body to CONTINUE. A string parameter used as a condition is a nil test.

CREATE FUNCTION describe(name text, n int) RETURNS text LANGUAGE plxlua AS $$
local who = name or "someone"
local evens = 0
for i = 1, n do
  if i % 2 == 1 then goto continue end
  evens = evens + 1
  ::continue::
end
local size = n > 10 and "many" or "few"
if not name then who = who .. " (anonymous)" end
return who .. " counted " .. size .. " numbers, " .. evens .. " even"
$$;
SELECT describe('Ada', 4), describe(NULL, 20);
            describe             |                     describe                      
---------------------------------+---------------------------------------------------
 Ada counted few numbers, 2 even | someone (anonymous) counted many numbers, 10 even

Iterating an array

ipairs over an array becomes FOREACH; the element type comes from the parameter’s type. table.concat is array_to_string, and string.format formats a value.

CREATE FUNCTION summarize(prices numeric[]) RETURNS text LANGUAGE plxlua AS $$
local total = 0.0
local parts = {} --:: text[]
for i, p in ipairs(prices) do
  total = total + p
  table.insert(parts, string.format("#%d=%.2f", i, p))
end
return table.concat(parts, " ") .. " total " .. string.format("%.2f", total)
$$;
SELECT summarize(ARRAY[1.5, 2.25, 10]);
              summarize               
--------------------------------------
 #1=1.50 #2=2.25 #3=10.00 total 13.75

Reducing a query into a string builder

Combine a query loop with the builder to fold rows into one value. Each name part is split with a SQL expression and its first letter is accumulated into initials.

CREATE FUNCTION initials(full_name text) RETURNS text LANGUAGE plxlua AS $$
local out = ""
for r in query("SELECT unnest(string_to_array($1, ' ')) AS part", full_name) do
  out = out .. r.part:sub(1, 1):upper()
end
return out
$$;
SELECT initials('grace brewster hopper');
 initials 
----------
 GBH