# 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](plxruby.md), [plxphp](plxphp.md), [plxjs](plxjs.md), [plxpython3](plxpython3.md), [plxcobol](plxcobol.md)). **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](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](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](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](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 = 5`; `%TYPE` and `%ROWTYPE` as `local e --:: t%ROWTYPE`; triggers as `NEW.col = e` and `return NEW`. Locals are block scoped. See [plxlua.md](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](#plxcsharp) table below lists its spelling of each construct. See [plxcsharp.md](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 "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 `. `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.