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 from WORKING-STORAGE PICTURE clauses or a TYPE clause.
  • Trigger functions: a function returning trigger runs as a trigger in every dialect. NEW, OLD, and the TG_ variables are available, and assignment to a record field (NEW.col = e) or array element is supported (the qualified/subscripted lvalue form; a bare NEW = e is not).
  • Function forms (verified across dialects): OUT and INOUT parameters work; assign to the parameter by name. RETURNS TABLE(...) works, but the table columns are OUT parameters, so emit a row with a bare emit / return_next (no argument); the argument form is only for RETURNS SETOF <scalar>. SELECT INTO with the strict fetch (fetch_one!) raises on zero or more than one row; the non-strict fetch_one returns all-NULL on no row. GET DIAGNOSTICS ROW_COUNT is exposed as row_count(); stacked-diagnostics fields are exposed via the exception accessors. Not surfaced yet: SCROLL cursors and the GET DIAGNOSTICS fields other than ROW_COUNT (PG_CONTEXT, PG_ROUTINE_OID).

This file is updated as constructs land.