Contents
- Lua cookbook
- Scalar function with branching
- Accumulating loop
- Building a string in a loop
- Looping over a query result
- A set-returning function
- Rows with several columns
- Trapping an error
- Handling one error condition
- Raising an error with a SQLSTATE
- A trigger function
- Dynamic SQL with bind parameters
- Lua idioms: or, and/or, continue
- Iterating an array
- Reducing a query into a string builder
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