Contents
plxruby: the Ruby dialect
plxruby lets you write PostgreSQL functions with Ruby syntax. 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 plxruby, so it does not collide with the native PL/Ruby.
Setup
CREATE EXTENSION plx;
Function basics
The body is a sequence of statements. A scalar function ends with an explicit
return.
CREATE FUNCTION add(a int, b int) RETURNS int LANGUAGE plxruby AS $$
return a + b
$$;
Function arguments are referenced by name. Any SQL expression is valid in a statement, because expressions are passed through to plpgsql and SQL unchanged.
Local variables and types
Assignment creates a local. plpgsql requires every local to have a type, so plx
infers it from the first value when it is an integer, numeric, text, or boolean
literal. Otherwise annotate the variable with #:: type.
n = 42 # inferred integer
label = "count" # inferred text
amount #:: numeric
amount = principal * rate
A first assignment of a constant literal at the top level is folded into the
declaration, so n = 42 becomes DECLARE n integer := 42;.
The annotation becomes the plpgsql type. It must be a type name and nothing
else (plx checks this at CREATE FUNCTION time), and %TYPE and %ROWTYPE
work:
e #:: emp%ROWTYPE
s #:: emp.sal%TYPE
A constant uses the const suffix on the annotation:
pi = 3.14159 #:: numeric const
A constant becomes a plpgsql CONSTANT, whose value is computed when the
function starts. So it can use literals, parameters and earlier constants, but
not another local variable: that would read the variable’s initial value, not
the one assigned above it, and is rejected.
Control flow
if / elsif / else, unless
if score >= 90
grade = "A"
elsif score >= 80
grade = "B"
else
grade = "F"
end
unless c is if not c. Modifier forms are supported: return x unless n != 0.
case / when
With a subject it is a simple CASE; without one it is a searched CASE.
case n
when 1
return "one"
when 2, 3
return "few"
else
return "many"
end
Loops
Integer range, while, until, and loop:
for i in 1..n # inclusive; use 1...n for exclusive
total = total + i
end
while n > 0
n = n - 1
end
loop do
break if done
end
next and break map to CONTINUE and EXIT, and accept a condition modifier
(next if i == 3).
As in Ruby, the variable of a for loop keeps its last value after the loop
ends or after a break. If the range is empty, the variable keeps the value it
had before the loop.
Loop labels
outer: for i in 1..n
for j in 1..n
break outer if i + j >= limit
end
end
Working with data
Iterating a query
query("SELECT id, amount FROM orders WHERE grp = #{g}").each do |row|
total = total + row.amount
end
The row variable is a record. Field access is row.col, row[:col], or
row['col']. The bracket form reads a field only on a record. On a jsonb or
hstore value it is SQL subscripting (j['a'] needs PostgreSQL 14 or later), and
ARRAY['x'] is an array. Interpolated values in the SQL string are bound as
values (see Interpolation in SQL). For bind parameters or non-literal SQL, pass extra arguments:
query(sql, a, b).each. each_with_index do |row, i| provides a zero-based
index.
Iterating an array
v #:: int
values.each do |v|
total = total + v
end
The loop variable must be annotated with its element type.
each_with_index do |v, i| works on a query, an array and a range
((1..n).each_with_index). The index i is an integer that starts at 0. As
in Ruby, the index exists only inside the block. A parameter or a variable
with the same name is hidden inside the block
and keeps its value after the loop. The value and
the index must have different names. Without each_with_index, a block takes
one variable: each do |v, i| is an error.
Fetching one row
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") # in a set-returning function
perform runs a literal statement. execute runs dynamic SQL with optional bind
arguments. Use bind arguments for untrusted input.
Cursors
c = open_cursor("SELECT v FROM t ORDER BY v")
row = fetch_from(c)
while found?
total = total + row.v
row = fetch_from(c)
end
close_cursor(c)
move_cursor(c) and move_cursor(c, n) map to MOVE.
Diagnostics
rc = row_count() # GET DIAGNOSTICS rc = ROW_COUNT
if found? # the FOUND variable
return rc
end
Set-returning functions
CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxruby AS $$
for i in 1..n
return_next i * i
end
return
$$;
emit is an alias for a bare return_next.
Errors
Raising
raise "bad value" # EXCEPTION
raise notice: "processed #{n} rows" # NOTICE
raise exception: "negative: #{v}", errcode: "22023"
Levels are notice, warning, info, log, debug, exception. Options are
errcode, detail, hint, column, constraint, message. message: takes
the place of the message, as in raise message: "bad #{v}", errcode: "22023";
CREATE FUNCTION rejects a raise that has both. The call form
raise("notice", "message") is also accepted.
Handling
begin
return 100 / d
rescue PG::UniqueViolation => e
raise notice: "dup: #{e.message} on #{e.constraint}"
return -1
ensure
perform("INSERT INTO log(msg) VALUES ('done')")
end
rescue => e (or bare rescue) is WHEN OTHERS. Exception classes map to
plpgsql conditions (PG::UniqueViolation to unique_violation, and similar).
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.
The ensure body runs once, whether the begin block completes, returns,
breaks, continues or raises an error. After an error it runs and then raises
the error again, with its SQLSTATE, message, detail, hint and the names of
the objects it is about (schema, table, column, constraint, type); the
CONTEXT of the raised error points at the re-raise, not the original
statement. It does not run when the statement is cancelled (a query cancel
or statement_timeout) or an assert fails, because WHEN OTHERS does not
catch those two errors; for the same reason rescue does not catch them.
Assertions
assert(n > 0, "must be positive")
Expressions
- Interpolation:
"total is #{amount}"becomes'total is ' || (amount)::text. Single-quoted strings do not interpolate. Adjacent literals,"a" 'b', are concatenated. - Comparison:
==and!=map to=and<>.x == nilbecomesx IS NULL. - Boolean:
&&,!,and,or, andnotmap toAND,NOT,AND,OR, andNOT.||maps toORonly in a condition (if,unless,while,until, or a ternary test); elsewhere it passes through as SQL||(string concatenation), so writeorthere. - Power and XOR:
a ** bbecomes((a) ^ (b)), so it keeps the dialect’s meaning:2 ** 3 ** 2is 512 and-2 ** 2is -4.a ^ bis bitwise XOR and becomes SQLa # b; it works on integers. SQL reads&,|,#,<<and>>from left to right, soCREATE FUNCTIONrejects a mix that Ruby groups differently, such asa ^ b & c. Writea ^ (b & c). - Ternary:
c ? a : bbecomesCASE WHEN c THEN a ELSE b END. - Casts:
x.to_sandx.to_fbecome::textand::double precision.x.to_ibecomestrunc((x)::numeric)::integer, so it truncates toward zero as Ruby does:2.7.to_iis 2 and-2.7.to_iis -2. A string such as"4.7"gives 4.nilbecomesNULL.
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 the
append operator <<, which plx lowers to its string builder (plx_strbuild):
s = "" #:: text
query("SELECT name FROM t ORDER BY id").each do |row|
s << row.name
s << ","
end
return s
If an appended value is NULL, the whole string becomes NULL, as with ||.
To skip NULL values, append coalesce(x, '').
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). Alternatively, assemble text in SQL with string_agg when the pieces
come from a query.
The builder is used only for a local declared text or varchar without a
length. A local declared with a length, such as s = "" #:: varchar(20), keeps
its type, and << becomes s := s || x, which raises value too long when the
text outgrows it. On an array local, << appends an element:
tags = a #:: text[]
tags << "new" # tags := array_append(tags, ('new'))
Trigger functions
A function returning trigger can be used as a trigger. Assign to NEW fields
and return NEW (or OLD, or nil):
CREATE FUNCTION stamp() RETURNS trigger LANGUAGE plxruby 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. - Comparisons use SQL three-valued logic:
==and!=map to=and<>, so a comparison involving NULL is unknown (nil == nilis not true). Only a comparison with the literalnilbecomesIS NULL/IS NOT NULL. A positiveif/whilecondition treats NULL as false. String#+remains SQL numeric+. Use interpolation for concatenation.- Interpolating a NULL propagates it, so
"x=#{v}"is NULL whenvis NULL, rather than the'x='that Ruby’snil.to_swould give. In a message being built forraisethe value falls back to an empty string instead, so one NULL cannot swallow the message. The same fallback applies to thedetail:,hint:,column:andconstraint:options ofraise. A NULLerrcode:still makes theraisefail withRAISE statement option cannot be null. A NULL in anassertmessage replaces it withassertion failed. Wrap such values incoalesce(x, ''). - Comparisons use SQL type resolution, not Ruby’s.
1 == "1"compares an integer to a string literal, which SQL coerces and treats as equal; in Ruby it is false. Compare like-typed values. - Integer division and modulo follow SQL (truncate toward zero):
-7 / 2is-3and-7 % 2is-1, where Ruby gives-4and1. - Locals are function-scoped, matching Ruby method scope.
- Ruby truthiness is not emulated. A condition must be a boolean expression;
write
x != 0orx != nilrather than a barex. A non-boolean condition is not rejected at CREATE time. When the function runs, plpgsql coerces it through its text form: a value whose text is a boolean literal (0,1,t,yes,off, …) is silently accepted, and anything else raisesinvalid input syntax for type boolean.
Not supported
Rejected at CREATE FUNCTION time with a line number:
- Method or class definitions (
def, classes, modules), gems. - Blocks and lambdas beyond the recognized
.eachforms. - Hash and array literals as general values.
||=and&&=.redo,retry.- Predicate/bang methods (
x.zero?,arr.empty?,s.strip!): the trailing?/!is not a SQL operator. Use the SQL form (x = 0,cardinality(arr) = 0). - 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.