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 == nil becomes x IS NULL.
  • Boolean: &&, !, and, or, and not map to AND, NOT, AND, OR, and NOT. || maps to OR only in a condition (if, unless, while, until, or a ternary test); elsewhere it passes through as SQL || (string concatenation), so write or there.
  • Power and XOR: a ** b becomes ((a) ^ (b)), so it keeps the dialect’s meaning: 2 ** 3 ** 2 is 512 and -2 ** 2 is -4. a ^ b is bitwise XOR and becomes SQL a # b; it works on integers. SQL reads &, |, #, << and >> from left to right, so CREATE FUNCTION rejects a mix that Ruby groups differently, such as a ^ b & c. Write a ^ (b & c).
  • Ternary: c ? a : b becomes CASE WHEN c THEN a ELSE b END.
  • Casts: x.to_s and x.to_f become ::text and ::double precision. x.to_i becomes trunc((x)::numeric)::integer, so it truncates toward zero as Ruby does: 2.7.to_i is 2 and -2.7.to_i is -2. A string such as "4.7" gives 4. nil becomes NULL.

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 == nil is not true). Only a comparison with the literal nil becomes IS NULL / IS NOT NULL. A positive if/while condition treats NULL as false.
  • String#+ remains SQL numeric +. Use interpolation for concatenation.
  • Interpolating a NULL propagates it, so "x=#{v}" is NULL when v is NULL, rather than the 'x=' that Ruby’s nil.to_s would give. In a message being built for raise the value falls back to an empty string instead, so one NULL cannot swallow the message. The same fallback applies to the detail:, hint:, column: and constraint: options of raise. A NULL errcode: still makes the raise fail with RAISE statement option cannot be null. A NULL in an assert message replaces it with assertion failed. Wrap such values in coalesce(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 / 2 is -3 and -7 % 2 is -1, where Ruby gives -4 and 1.
  • Locals are function-scoped, matching Ruby method scope.
  • Ruby truthiness is not emulated. A condition must be a boolean expression; write x != 0 or x != nil rather than a bare x. 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 raises invalid 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 .each forms.
  • 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.