Contents
plxphp: the PHP dialect
plxphp lets you write PostgreSQL functions with PHP 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 plxphp, so it does not collide with the native PL/PHP.
Setup
CREATE EXTENSION plx;
Function basics
Blocks are brace-delimited and statements end with ;. A scalar function ends
with an explicit return.
CREATE FUNCTION add(a int, b int) RETURNS int LANGUAGE plxphp AS $$
return $a + $b;
$$;
Function arguments are referenced with the $ sigil, which is removed in the
generated plpgsql. Any SQL expression is valid, because expressions are passed
through to plpgsql and SQL unchanged.
Local variables and types
Assignment creates a local. plx infers the type from the first value when it is
an integer, numeric, text, or boolean literal. Otherwise annotate the variable
with a /*:: type */ comment placed before the ;. An annotation before an initializer
($n /*:: bigint */ = 3;) is rejected; write $n = 3 /*:: bigint */;.
$n = 42; // inferred integer
$label = "count"; // inferred text
$amount /*:: numeric */;
$amount = $principal * $rate;
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 in 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 / elseif / else
if ($score >= 90) { $grade = "A"; }
elseif ($score >= 80) { $grade = "B"; }
else { $grade = "F"; }
else if is also accepted.
switch
switch lowers to plpgsql CASE. Stacked labels become a value list. Each arm
must end with break or another statement that leaves the arm (return,
throw, break N, continue N with N of 2 or more, or break with a loop
label); genuine fall-through is rejected.
A break inside an if or try within an arm leaves the switch, as it
does in the source language. A break inside a loop within an arm leaves only
that loop.
switch ($n) {
case 1: return "one";
case 2: case 3: return "few";
default: return "many";
}
Loops
Counting for and while:
for ($i = 1; $i <= $n; $i++) {
$total = $total + $i;
}
while ($n > 0) { $n = $n - 1; }
A counting for becomes a plpgsql integer FOR. That loop works out its
bounds once and keeps its own copy of the loop variable. So plx accepts a for
only when the result is the same:
- The condition compares the loop variable with a single value (
$i < $nor$i <= $n). - The step changes the loop variable (
$i++or$i += K). - The body does not assign the loop variable, or any variable in the bound or the step.
- The code after the loop does not read the loop variable before it assigns
it again. A later
for ($i = ...)assigns it. - When the
foris inside another loop, that loop does not read the loop variable on its next pass before theforruns again: not in its condition, and not in its body before thefor.
CREATE FUNCTION rejects any other for. Write it as a while loop instead.
continue and break map to CONTINUE and EXIT.
break N and continue N leave or continue the Nth enclosing loop or
switch, counting from the innermost, as in PHP:
for ($i = 0; $i < $n; $i++) {
for ($j = 0; $j < $n; $j++) {
if ($j == 1) { break 2; } // leaves both loops
}
}
CREATE FUNCTION rejects a level of 0 and a level greater than the number of
enclosing loops and switches. It also rejects a continue N that lands on a
switch; write break N instead.
PHP treats a switch as a loop for continue. So a continue or
continue 1 in a switch arm, and not in a loop within that arm, acts as
break, not as a continue of the enclosing loop. CREATE FUNCTION rejects it. Write break
to leave the switch, or continue 2 to continue the loop.
Loop labels
outer: for ($i = 1; $i <= $n; $i++) {
for ($j = 1; $j <= $n; $j++) {
if ($i + $j >= $lim) { break outer; }
}
}
Working with data
Iterating a query
foreach (query("SELECT id, amount FROM orders WHERE grp = {$g}") as $row) {
$total = $total + $row->amount;
}
The row variable is a record. Field access is $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). For
bind parameters or non-literal SQL, pass extra arguments:
query($sql, $a) as $row.
Iterating an array
$v /*:: int */;
foreach ($values as $v) {
$total = $total + $v;
}
The loop variable must be annotated with its element type.
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"); // set-returning function
Use bind arguments in execute 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);
}
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()) { return $rc; } // the FOUND variable
Set-returning functions
CREATE FUNCTION squares(n int) RETURNS SETOF int LANGUAGE plxphp AS $$
for ($i = 1; $i <= $n; $i++) { return_next($i * $i); }
return;
$$;
Errors
Throwing
throw new Exception("negative: {$v}");
throw takes a constructor call, such as new Exception(message), and raises its first
argument as the error message. CREATE FUNCTION rejects any other thrown
expression, such as throw msg; or throw a + b(c);. To throw a computed
message, build it inside the call.
The call form raise("notice", "message") emits a RAISE at the named level
(notice, warning, info, log, debug, exception).
Handling
try {
execute("INSERT INTO uniq(id) VALUES ($1)", $k);
} catch (\Exception $e) {
raise("notice", "dup on " . $e->constraint);
} finally {
perform("INSERT INTO log(msg) VALUES ('done')");
}
catch (\Exception $e) maps to WHEN OTHERS, and so do \Throwable and
\Error. To catch a specific condition, write PG:: and then the plpgsql
condition name in CamelCase. For example, catch (PG::UniqueViolation $e) maps
to unique_violation, and catch (PG::SerializationFailure $e) maps to
serialization_failure. DivisionByZeroError maps to division_by_zero. To
catch several conditions in one handler, join the classes with |, as in
catch (PG::UniqueViolation | PG::CheckViolation $e). The variable is
optional, as in PHP 8: catch (PG::SerializationFailure) maps the class the
same way.
CREATE FUNCTION rejects any other class name, such as TypeError, with
unsupported exception class. plpgsql checks a PG:: condition name the first
time the function runs. An unknown name, such as PG::NoSuchCondition, then
fails with unrecognized exception condition "no_such_condition".
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 finally body runs once, whether the try 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 catch (\Exception $e) does not
catch them.
Assertions
assert($n > 0, "must be positive");
Expressions
- Interpolation:
"total is $amount"and"count {$n}"splice the value. As in PHP, the simple form also takes one property or element after the name:"$row->name","$a[1]","$a[$i]", and"$row[name]"(the same as{$row['name']}). For anything longer, such as$row->a->b, use the braced form. Single-quoted strings do not interpolate. - Concatenation:
$a . $bmaps to$a || $b. - Comparison:
==,===,!=,!==map to=and<>.$x == nullbecomes$x IS NULL. - Boolean:
&&and!map toANDandNOT.||maps toORonly in anif/whilecondition or a ternary test. Elsewhere (assignments,return) it passes through as SQL||(string concatenation), so writeORthere:$ok = $a OR $b /*:: boolean */;. - 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 PHP groups differently, such as$a ^ $b & $c. Write$a ^ ($b & $c). - Ternary:
$c ? $a : $bbecomesCASE WHEN $c THEN $a ELSE $b END. nullbecomesNULL.
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 assignment .=, which plx lowers to its string builder (plx_strbuild):
$s = "" /*:: text */;
foreach (query("SELECT name FROM t ORDER BY id") as $row) {
$s .= $row->name;
$s .= ",";
}
return $s;
If an appended value is NULL, the whole string becomes NULL, as with ||.
To skip NULL values, append coalesce(x, '').
A variable whose first use is .= starts as an empty string, as in PHP.
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).
Trigger functions
A function returning trigger can be used as a trigger. Assign to $NEW fields
and return $NEW:
CREATE FUNCTION stamp() RETURNS trigger LANGUAGE plxphp AS $$
$NEW->tag = "row {$NEW->id}";
return $NEW;
$$;
$NEW, $OLD, and the TG_ variables are available. Assigning to a record
field works with the arrow form ($NEW->col = e) or the array-element form
($NEW['col'] = e).
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:
==,===,!=,!==map to=and<>, so a comparison involving NULL is unknown. Only a comparison with the literalnullbecomesIS NULL/IS NOT NULL. A positiveif/whilecondition treats NULL as false. ===and!==do not preserve PHP strict-type semantics; they behave like==and!=and use SQL type resolution."1" === 1is false in PHP but compares as equal here. Compare like-typed values.- Interpolating a NULL propagates it, so the whole string becomes NULL rather
than rendering the value as an empty string. In a
throworraisemessage, including theraise("level", ...)call form, the value falls back to an empty string instead, so one NULL cannot swallow the message. Anassertmessage gets no fallback: a NULL there replaces your message with plpgsql’s genericassertion failed. - Integer division and modulo follow SQL (truncate toward zero).
- PHP truthiness is not emulated. A condition must be a boolean expression. 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. - Locals are function-scoped, matching PHP function scope.
Not supported
Rejected at CREATE FUNCTION time with a line number:
- Function or class definitions, namespaces,
use, includes. - A
forthat is not a counting loop. Onlyfor ($v = LO; $v < HI; $v++)andfor (...; $v += K)are supported, under the rules in Loops. switchfall-through (end each case withbreakorreturn).
Not translated:
- Closures, anonymous functions, and array and object literals as general
values. An unannotated assignment of one fails at
CREATE FUNCTIONtime (cannot infer a PostgreSQL type); an annotated one passes through and fails when the function runs. foreach ($a as $k => $v): the key is ignored; only value iteration is supported.
See PARITY.md for the full plpgsql construct matrix and ARCHITECTURE.md for how plx maps to the plpgsql engine.