# 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 ```sql CREATE EXTENSION plx; ``` ## Function basics The body is a sequence of statements. A scalar function ends with an explicit `return`. ```sql 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`. ```sql 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: ```sql e #:: emp%ROWTYPE s #:: emp.sal%TYPE ``` A constant uses the `const` suffix on the annotation: ```sql 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 ```sql 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`. ```sql case n when 1 return "one" when 2, 3 return "few" else return "many" end ``` ### Loops Integer range, `while`, `until`, and `loop`: ```sql 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 ```sql 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 ```sql 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](DYNAMIC-SQL.md)). 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 ```sql 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 ```sql 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 ```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 ```sql 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 ```sql rc = row_count() # GET DIAGNOSTICS rc = ROW_COUNT if found? # the FOUND variable return rc end ``` ### Set-returning functions ```sql 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 ```sql 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 ```sql 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 ```sql 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(n^2) because text is immutable and each step copies the whole string. Use the append operator `<<`, which plx lowers to its string builder (`plx_strbuild`): ```sql 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: ```sql 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`): ```sql 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](PARITY.md) for the full plpgsql construct matrix and [ARCHITECTURE.md](ARCHITECTURE.md) for how plx maps to the plpgsql engine.