Contents
plx / plpgsql Feature Parity
Target: every plpgsql capability is expressible in each plx dialect. The
reference is the plpgsql statement set in src/pl/plpgsql/src/plpgsql.h
(PLpgSQL_stmt_*) plus its declaration forms.
Every construct below is supported in each column dialect (plxruby, plxphp, plxjs, plxpython3, plxcobol) except where a footnote notes an equivalent form. The cells show the dialect spelling; full syntax and semantics are in the per-dialect chapters (plxruby, plxphp, plxjs, plxpython3, plxcobol).
plxplsql (Oracle PL/SQL) is not shown as a separate column because its
spelling is the plpgsql form: PL/SQL and plpgsql share IF/ELSIF, LOOP,
WHILE, FOR, CASE, :=, ||, RAISE, cursors, %TYPE, and the
EXCEPTION section, so those constructs pass through unchanged. plxplsql adds
the Oracle spellings (DBMS_OUTPUT.PUT_LINE, RAISE_APPLICATION_ERROR, EXECUTE
IMMEDIATE, NVL, seq.NEXTVAL, NUMBER/VARCHAR2, …) on top. See
plxplsql.md.
plxts (TypeScript) is the plxjs column plus let x: T type annotations
(mapped to SQL types); every construct is the plxjs spelling. See
plxts.md.
plxgo (Go) is not shown as a separate column because it, too, restructures:
var/:= declarations (with type inference) hoist into the DECLARE block,
parenless if/for headers become IF ... THEN/WHILE ... LOOP, for range
becomes FOR/FOREACH, the no-fallthrough switch becomes IF/ELSIF, and
panic/fmt.Println become RAISE. Every plpgsql construct except
transaction control is reachable through Go surface syntax. See
plxgo.md.
plxtsql (Transact-SQL, SQL Server) is not shown as a separate column because
it restructures rather than renames: @x variables hoist to the DECLARE block,
IF/WHILE bodies written as BEGIN ... END become THEN ... END IF /
LOOP ... END LOOP, SET @x = e becomes x := e, SELECT @x = c FROM t
becomes a FOR loop over the query that assigns x once per row, PRINT/RAISERROR/THROW become RAISE, and
TRY/CATCH becomes an EXCEPTION block. So most plpgsql constructs are
reachable through T-SQL surface syntax rather than a one-to-one keyword map;
the exceptions are cursors and transaction control, which plxtsql does not
support.
See plxtsql.md.
plxlua (Lua) is not shown as a separate column. It keeps Lua’s
keyword-end blocks and reaches each construct as follows: assignment
x = e; IF as if ... then ... elseif ... else ... end; simple and searched
CASE as if/elseif (footnote ¹); LOOP as while true do or
repeat ... until false; WHILE as while c do; integer FOR as
for i = a, b [, step] do; FOR over a query, static or dynamic, as
for r in query(sql, ...) do; FOREACH as for i, v in ipairs(arr) do;
EXIT and CONTINUE as break and goto continue with ::continue:: ending
the body; a loop label as a goto to an outer loop’s ::continue:: or to a
label right after a loop; RETURN as return e; RETURN NEXT as emit(v);
RETURN QUERY as return_query(sql); PERFORM, EXECUTE, SELECT INTO,
cursors, CALL, COMMIT and ROLLBACK with the shared call-form intrinsics;
RAISE as error(m), error{message = ..., sqlstate = ...},
raise("notice", m) or print(...); ASSERT as assert(c, m); exception
handling as local ok, err = pcall(function() ... end), with a specific
condition tested through err.sqlstate; GET DIAGNOSTICS as row_count();
FOUND as found(); GET STACKED DIAGNOSTICS as err.detail,
err.constraint, …; CONSTANT as local x <const> = 5; %TYPE and
%ROWTYPE as local e --:: t%ROWTYPE; triggers as NEW.col = e and
return NEW. Locals are block scoped. See plxlua.md.
plxcsharp (C#) is not shown as a separate column because most of its
spellings are those of the plxjs column, with C# declarations and statements on
top: type-first declarations (int x = 0;) hoist into the DECLARE block,
foreach iterates a query or an array, switch sections end in break,
do/while and the general for keep their C# semantics, yield return is
RETURN NEXT, and throw and catch (exception classes and when filters)
become RAISE and exception conditions. The plxcsharp table
below lists its spelling of each construct. See plxcsharp.md.
Control flow
| plpgsql | plxruby | plxphp | plxjs | plxpython3 | plxcobol |
|---|---|---|---|---|---|
assignment (:=) |
x = e |
$x = e; |
x = e; |
x = e |
MOVE e TO x · COMPUTE x = e |
IF/ELSIF/ELSE |
if/elsif/else/end |
if/elseif/else |
if/else if/else |
if/elif/else: |
IF/ELSE/END-IF |
simple CASE |
case x/when |
switch/case |
switch/case |
if/elif ¹ | EVALUATE/WHEN |
searched CASE |
case/when (no subject) |
if/else ¹ | if/else ¹ | if/elif ¹ | EVALUATE TRUE/WHEN |
LOOP |
loop do/end |
while (true) |
while (true) |
while True: |
PERFORM/END-PERFORM |
WHILE |
while · until |
while |
while |
while |
PERFORM UNTIL c |
integer FOR |
for i in 1..n |
for ($i=…; …; $i++) |
for (let i=…; …; i++) |
for i in range(…) |
PERFORM VARYING · PERFORM n TIMES |
FOR over query |
query(sql).each do |r| |
foreach (query(sql) as $r) |
for (const r of query(sql)) |
for r in query(sql): |
PERFORM r OVER "sql" |
FOR over dynamic query |
same, non-literal SQL / binds | same | same | same | … OVER "sql" USING … |
FOREACH over array |
arr.each do |v| |
foreach ($a as $v) |
for (const v of arr) |
for v in arr: |
PERFORM v OVER ARRAY a |
EXIT / CONTINUE |
break / next |
break / continue |
break / continue |
break / continue |
EXIT PERFORM / EXIT PERFORM CYCLE |
| loop labels ² | label: + break label |
label: + break label |
label: (native) |
label: + break label |
not offered |
¹ Python has no match/case, and PHP/JS switch is a simple CASE only, so a
searched (arbitrary-condition) or Python multi-way branch uses if/elif(else
if)/else, the same control flow, lowered to plpgsql IF. Ruby case and
COBOL EVALUATE / EVALUATE TRUE express both simple and searched CASE
directly.
² Loop labels (label: before a loop with break label / next label) are
native in JavaScript and a plx extension in Ruby, PHP, and Python; not offered
in COBOL.
Return, SQL, and data access
| plpgsql | plxruby | plxphp | plxjs | plxpython3 | plxcobol |
|---|---|---|---|---|---|
RETURN |
return e |
return $e; |
return e; |
return e |
GOBACK RETURNING e |
RETURN NEXT |
emit e · return_next e |
return_next($e) |
return_next(e) |
return_next(e) |
RETURN-NEXT e |
RETURN QUERY |
return_query(sql) |
return_query(sql) |
return_query(sql) |
return_query(sql) |
RETURN-QUERY "sql" |
PERFORM |
perform(sql) |
perform(sql) |
perform(sql) |
perform(sql) |
EXECUTE "sql" |
EXECUTE (dynamic) |
execute(sql, a) |
execute(sql, $a) |
execute(sql, a) |
execute(sql, a) |
EXECUTE "sql" USING a |
SQL / SELECT INTO |
fetch_one(sql) · fetch_one! |
fetch_one(sql) |
fetch_one(sql) |
fetch_one(sql) |
EXECUTE "sql" INTO v |
cursors OPEN/FETCH/MOVE/CLOSE |
open_cursor · fetch_from · move_cursor · close_cursor |
(same call form) | (same call form) | (same call form) | OPEN- · FETCH- · MOVE- · CLOSE-CURSOR |
CALL procedure |
call("p", a) |
call("p", $a) |
call("p", a) |
call("p", a) |
CALL "p" USING a |
COMMIT / ROLLBACK (procedures only) |
commit() / rollback() |
(same) | (same) | (same) | COMMIT / ROLLBACK |
The call-form intrinsics (perform, execute, fetch_one, the *_cursor
functions, call, commit, rollback, return_next, return_query) share the
same name in plxruby, plxphp, plxjs, and plxpython3; only the argument sigils and
string-literal/interpolation syntax differ. COBOL uses dedicated verbs.
Errors and diagnostics
| plpgsql | plxruby | plxphp | plxjs | plxpython3 | plxcobol |
|---|---|---|---|---|---|
RAISE |
raise "m" · raise notice: "m" |
throw … · raise("notice", "m") |
throw … · raise("notice", "m") |
raise ValueError(…) · raise("notice", "m") |
RAISE <level> "m" · DISPLAY |
ASSERT |
assert(c, m) |
assert(c, m) |
assert(c, m) |
assert c, m |
ASSERT c |
| exception handling | begin/rescue/ensure/end |
try/catch/finally |
try/catch/finally |
try/except/finally |
BEGIN-TRY/WHEN/END-TRY |
GET DIAGNOSTICS (ROW_COUNT) |
row_count() |
row_count() |
row_count() |
row_count() |
GET ROW-COUNT INTO v |
FOUND |
found? |
found() |
found() |
found() |
FOUND |
GET STACKED DIAGNOSTICS |
e.message · e.detail · … |
$e->message · $e->detail · … |
e.message · e.detail · … |
e.message · e.detail · … |
GET MESSAGE · GET DETAIL · … INTO v |
Stacked-diagnostics fields available in all dialects: message (SQLERRM),
sqlstate (SQLSTATE), detail, hint, constraint, column, table,
schema, datatype. A specific condition is caught by name
(PG::UniqueViolation / \PG::UniqueViolation / WHEN unique-violation).
Declarations and triggers
| plpgsql | plxruby | plxphp | plxjs | plxpython3 | plxcobol |
|---|---|---|---|---|---|
CONSTANT |
x = 5 #:: int const |
$x = 5 /*:: int const */ |
let x = 5 /*:: int const */ |
x = 5 #:: int const |
01 X CONSTANT AS 5 |
%TYPE / %ROWTYPE |
e #:: t%ROWTYPE |
$e /*:: t%ROWTYPE */ |
let e /*:: t%ROWTYPE */ |
e #:: t%ROWTYPE |
01 E TYPE t%ROWTYPE |
trigger (NEW/OLD/TG_) |
NEW.col = e; return NEW |
$NEW->col = e; return $NEW; |
NEW.col = e; return NEW; |
NEW.col = e; return NEW |
MOVE e TO NEW.col; GOBACK RETURNING NEW |
nested block DECLARE ³ |
function-scoped | function-scoped | function-scoped | function-scoped | function-scoped |
³ Locals are function-scoped in every dialect, matching the source language’s
scoping (Ruby method locals, JavaScript var, PHP function scope, Python
function scope, COBOL WORKING-STORAGE). plpgsql’s per-block DECLARE is not a
separate construct; an exception block (begin/rescue, try/catch, BEGIN-TRY)
provides the nested BEGIN/EXCEPTION/END structure sharing the function scope.
plxcsharp
| plpgsql | plxcsharp |
|---|---|
assignment (:=) |
x = e; |
IF/ELSIF/ELSE |
if/else if/else |
simple CASE |
switch/case/default · x switch { 1 => a, _ => b } |
searched CASE |
if/else · x switch { < 0 => a, _ => b } |
LOOP |
for (;;) · while (true) |
WHILE |
while (c) · do { } while (c); |
integer FOR |
for (int i = a; i < b; i++) |
FOR over query |
foreach (var r in query(sql)) |
FOR over dynamic query |
same, with bind arguments or ident()/raw() |
FOREACH over array |
foreach (int v in arr) |
EXIT / CONTINUE |
break / continue |
| loop labels | not offered (C# has none) |
RETURN |
return e; |
RETURN NEXT |
yield return e; · return_next(); |
RETURN QUERY |
return_query(sql) |
PERFORM |
perform(sql) |
EXECUTE (dynamic) |
execute(sql, a) |
SQL / SELECT INTO |
var r = fetch_one(sql); · long n = fetch_one(sql); · fetch_one! |
cursors OPEN/FETCH/MOVE/CLOSE |
open_cursor · fetch_from · move_cursor · close_cursor |
CALL procedure |
call("p", a) |
COMMIT / ROLLBACK |
commit() / rollback() |
RAISE |
throw new Exception(m) · Console.WriteLine(m) · raise("notice", m) |
ASSERT |
Debug.Assert(c, m) · assert(c, m) |
| exception handling | try/catch (Exception e)/finally |
| a specific condition | catch (DivideByZeroException e) · catch (PostgresException e) when (e.SqlState == "23505") |
GET DIAGNOSTICS (ROW_COUNT) |
row_count() |
FOUND |
found() |
GET STACKED DIAGNOSTICS |
e.Message · e.SqlState · e.Detail · e.ConstraintName · … |
CONSTANT |
const int x = 5; |
%TYPE / %ROWTYPE |
a table name as the type (orders r;); no %TYPE |
trigger (NEW/OLD/TG_) |
NEW.col = e; return NEW; |
nested block DECLARE |
function-scoped |
Notes
- Type inference: an integer, numeric, text, or boolean literal infers the local
type in plxruby/plxphp/plxjs/plxpython3; otherwise annotate it (
#:: type,/*:: type */). plxcobol declares types fromWORKING-STORAGEPICTUREclauses or aTYPEclause. - Trigger functions: a function returning
triggerruns as a trigger in every dialect.NEW,OLD, and theTG_variables are available, and assignment to a record field (NEW.col = e) or array element is supported (the qualified/subscripted lvalue form; a bareNEW = eis not). - Function forms (verified across dialects):
OUTandINOUTparameters work; assign to the parameter by name.RETURNS TABLE(...)works, but the table columns areOUTparameters, so emit a row with a bareemit/return_next(no argument); the argument form is only forRETURNS SETOF <scalar>.SELECT INTOwith the strict fetch (fetch_one!) raises on zero or more than one row; the non-strictfetch_onereturns all-NULL on no row.GET DIAGNOSTICS ROW_COUNTis exposed asrow_count(); stacked-diagnostics fields are exposed via the exception accessors. Not surfaced yet:SCROLLcursors and theGET DIAGNOSTICSfields other thanROW_COUNT(PG_CONTEXT,PG_ROUTINE_OID).
This file is updated as constructs land.