Contents
Gaps and limitations
plx transpiles a dialect function body to plpgsql at CREATE FUNCTION time, and
the standard plpgsql interpreter runs it. That design sets a hard boundary: a plx
function can express what plpgsql can express, in the surface syntax of the
dialect, and nothing more. This page collects the constraints that apply to every
dialect, then what each individual dialect does not support. Each per-dialect
chapter has the authoritative, complete list; the summaries here link to it.
Most of these are deliberate. plx pins semantics to SQL and plpgsql rather than emulating the source language’s runtime, so a function behaves the same no matter which dialect it was written in.
Constraints shared by every dialect
These follow from running as plpgsql and are not specific to any one dialect.
- Trusted sandbox. A plx function does what a plpgsql function can do and no
more: no filesystem access, no network access, no arbitrary native code. SQL
runs with the privileges of the calling role, or of the function owner for a
SECURITY DEFINERfunction, exactly as in plpgsql. This is the point of the design, and the reason plx is a trusted language while the native PL/Ruby or PL/PHP are not. Because plx is a syntax layer over plpgsql, USAGE on a plx language is equivalent to USAGE on plpgsql; see Trust and security. - Transaction control only in procedures. A function runs inside the
caller’s transaction, so
COMMITandROLLBACKraiseinvalid transaction terminationthere. In aCREATE PROCEDUREinvoked withCALL(outside an explicit transaction block),commit()/rollback()(COBOLCOMMIT/ROLLBACK, PL/SQLCOMMIT;) work as in plpgsql. plxtsql and plxgo do not support transaction control. Savepoint management is not available in any dialect. - The signature is SQL. The parameter and return types in
CREATE FUNCTIONare parsed by PostgreSQL before plx sees the body, so they must be PostgreSQL type names (integer,numeric,text,timestamp, …). Dialect type names (NUMBER,int64,let x: number) apply only inside the body. - A value interpolated as an
UPDATE ... SETtarget names a column. In a static SQL call, a value interpolated where SQL needs a name is written in parentheses, soFROM #{t}fails atCREATE FUNCTION.UPDATE t SET #{c} = vbecomesSET (c) = v, which PostgreSQL reads as a one-column list. With a plain valuevthe statement fails when it runs, withsource for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression. With a sub-SELECTorROW()it updates the column namedc, whatever the variablecholds. To choose the column at run time, write#{ident(c)}. See Dynamic SQL. - Function-scoped locals. plx hoists every local declaration into a single
plpgsql
DECLAREblock, so locals are visible for the whole function. There is no per-block (per-{}, per-if) scope, and a name cannot be redeclared with a new type partway through. - Names that differ only by case are one name. plpgsql ignores case in
names, so
totalandTotalwould be the same variable. In plxruby, plxphp, plxjs, plxts, plxpython3 and plxgo, where case matters,CREATE FUNCTIONrejects a body that spells one name two ways, for example a localAbeside a local or parametera, a loop variableIbeside a locali, or a loop labelouteron a loop inside a loop labelledOuter. Rename one of them. SQL folds an unquoted parameter name to lower case, so a parameter declared asuserIdmay be writtenuserIdin the body, as long as the body always spells it the same way. - No SQL reserved word as a variable name. plx writes variable names into
the generated SQL without quotes. A variable named after a reserved word,
such as
array, makesCREATE FUNCTIONfail with a syntax error. Whencheck_function_bodiesis off, the function is created and fails with the syntax error when it runs. Rename the variable. - No NUL character in a string. A PostgreSQL text value cannot hold the
byte 0, so
CREATE FUNCTIONrejects a string escape that produces it, such as\0or\x00. Other escapes keep the meaning they have in the dialect:"\x41"isAand"\v"is a vertical tab. - Parentheses in a mix of bitwise operators. SQL gives
&,|,#(XOR),<<and>>one precedence and reads them from left to right. In plxruby, plxphp, plxjs, plxts and plxpython3,CREATE FUNCTIONrejects a mix that the dialect would group differently, such asa ^ b & cora | b & c. Writea ^ (b & c). A mix that SQL groups the same way, such asa & b | c, is accepted. - One assignment per statement. In plxruby, plxphp, plxjs, plxts and
plxpython3,
CREATE FUNCTIONrejects a chained assignment such asa = b = 0,a += b = 1ora = b <<= 2. Assign each variable in its own statement. - SQL semantics, not the source language’s. Decimal literals are exact
numeric, not IEEE 754 floating point. Comparisons use SQL three-valued logic, so a comparison involving NULL is unknown rather than false, and==/===become=. Integer division and modulo truncate toward zero. Conditions must be boolean expressions; source-language “truthiness” (a bare value used as a condition) is not emulated. A non-boolean condition is not rejected atCREATE FUNCTIONtime. 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. Always write an explicit comparison. Interpolating a NULL propagates it: the value is concatenated as-is, so the whole string becomes NULL the way SQL||does. A message being built forRAISEis an exception: it keeps each interpolated value as an empty string, so one NULL cannot swallow the message. The append operators follow the same rule.s << x,$s .= $xands += xgive NULL when x or s is NULL, ass := s || xdoes. This differs from PHP, where.=treats null as an empty string, and from JavaScript, where it appends the text “null”. Usecoalesce(x, '')to treat a NULL as empty. A PHP variable whose first use is.=starts as an empty string. A message built with appends is not covered by the RAISE exception: if a part was NULL, the raised message is<NULL>. plxcobol’sSTRING-APPENDdoes not follow the append rule: it treats a NULL as nothing to append, because COBOL has no NULL. - A NULL errcode makes RAISE fail. In plxruby, a NULL value in the
detail:,hint:,column:orconstraint:option ofraisebecomes an empty string, the same as in the message, so your error is still raised with its own SQLSTATE. A NULLerrcode:is not replaced, because plx will not make up a SQLSTATE. Theraisethen fails withRAISE statement option cannot be null(SQLSTATE 22004) instead of raising your error. A NULL in anassertmessage replaces your message with plpgsql’s genericassertion failed. Wrap such values incoalesce(x, ''). - String concatenation with
+is not string concatenation. In every dialect except plxtsql and plxgo,+is SQL numeric addition. Use the dialect’s string form: interpolation (Ruby/PHP/Python/JS/TS),||(PL/SQL), orCONCAT(...). In plxcobol||is rejected. plxtsql writes+as||only when every operand is a known string, such as aVARCHARvariable or a string literal; see plxtsql.md. plxgo writes+as||only when every operand is a known string, such as a string literal or astringlocal; see plxgo.md. In plxgo||is logical OR. - Type inference is limited. Where a dialect infers a local’s type from its
initializer (
x = 5,x := 5,let x = ...), plx infers only from a literal (int, decimal, string, boolean), an array literal, or a direct type conversion. When the right-hand side is a call or a compound expression, declare the type explicitly (x #:: int,var x T,let x: T, aDECLARE). - String-builder acceleration needs PostgreSQL 18. plx lowers the dialect
append operators (
<<,.=,+=,STRING-APPEND) ontoplx_strbuildfor amortized-O(1) building. The in-place path relies on a PostgreSQL 18 planner hook; on 13 through 17 the result is correct but the append is O(n2), the same as plain concatenation. See COMPATIBILITY.md. - Only
textandvarcharlocals use the string builder. The append operators turn a local into aplx_strbuildonly when it has no declared type or is declaredtextorvarcharwithout a length. A local declared with another string type, such asvarchar(20),char(4)orcitext, keeps that type. Its append becomess := s || x, so plpgsql still checks the length (value too long for type character varying(20)) and a NULL value makes the result NULL. That append is not accelerated. In plxruby,<<on an array local appends an element (array_append). In plxphp,.=on an array local is rejected atCREATE FUNCTIONtime. - Choose plx’s schema when you install it. Functions that use the append
operators name the extension’s
plx_strbuildtype and its append function with the schema plx is installed in, so they work whatever theirsearch_pathis. The schema name is stored in each such function, so:ALTER EXTENSION plx SET SCHEMAfails withextension "plx" does not support SET SCHEMA. Give the schema inCREATE EXTENSION plx SCHEMA ....- A function restored into a database where plx is in another schema (from
a
pg_dump -nof one schema, say) fails when it runs. The error isschema "..." does not existwhen that schema is missing in the target, and otherwisetype "public.plx_strbuild" does not existorfunction public.plx_tsql_int4(record) does not exist, with the source schema in place ofpublic. Install plx in the same schema in the target database, or run the function’sCREATE FUNCTIONagain. - A function created before 3.0.0 keeps the unqualified names it was
generated with. Until you run its
CREATE FUNCTIONagain, it needs plx’s schema on itssearch_path.
plxtsql does the same for a conversion to an integer type (
CAST,CONVERTor an assignment to an integer variable) of a value whose type it cannot see, such as a column or a function call: it callsplx_tsql_int2,plx_tsql_int4orplx_tsql_int8with plx’s schema. Other functions are not affected. See Trust and security. - Runtime errors point at the generated plpgsql. plpgsql requires all
declarations at the top, so line numbers in a runtime error refer to the
transpiled body, not your dialect source.
plx_source()recovers the embedded original, and the mapping is usually close. See DEBUGGING.md. - A depth cap guards pathological input. Deeply nested expressions or
statements (hundreds of levels) are rejected at
CREATE FUNCTIONtime with anested too deeplyerror rather than risking a stack overflow. - What is always reachable. Every plpgsql statement type (assignment,
IF/CASE, all loops,RETURN/set-returning, cursors, dynamic SQL,RAISE, exception handling, triggers,GET DIAGNOSTICS) is expressible from every dialect, except that plxtsql has no cursors or transaction control and plxgo has no transaction control. See PARITY.md for the construct-by-construct matrix.
A dialect gives you its syntax over SQL semantics, not its standard library.
With the exception of the fmt/strings/math/strconv subset plxgo maps, a
method or library call in a body is passed through and has to name a function
PostgreSQL actually has. s.upcase, s.toUpperCase(), s.upper() and
strtoupper($s) all transpile without complaint and then fail when the
function is called; write upper(s) instead. The same applies to any other
library call you would reach for out of habit. A call that happens to name a
PostgreSQL function runs that function, which can give a different answer
with no error: plxphp count($arr) is SQL’s aggregate count() and returns
1 for any array. Write cardinality($arr).
What each dialect does not support
The per-dialect chapter is authoritative; this is a quick reference. Some
items are rejected at CREATE FUNCTION time; others (closures, literals of
unsupported types, library calls) are not translated, so they pass through and
fail when the function runs.
plxruby (chapter)
- Method, class, and module definitions (
def,class,module); gems. - Blocks and lambdas beyond the recognized
.eachforms. - Hash and array literals as general values.
||=and&&=.redo,retry.- Predicate and bang methods (
x.zero?,arr.empty?,s.strip!); use the SQL form (x = 0,cardinality(arr) = 0).
plxphp (chapter)
- Function, class, and namespace definitions;
use, includes, closures. - Array and object literals as general values.
- A
forthat is not a counting loop. Onlyfor ($v = LO; $v < HI; $v++)and+= Ksteps, under the rules in the chapter. foreachover a key/value pair: the key is ignored (value iteration only).switchfall-through (end each case withbreakorreturn).
plxjs (chapter)
- Function, arrow-function, and class definitions;
import. - Object and array literals as general values.
for...in.for...oftreats anything other thanquery(...)as an array, so a non-array fails when the function runs.- A
forthat is not a counting loop (chapter). - Block scoping:
let/constin an inner block assign the function-scoped variable of the same name. switchfall-through.
plxts (chapter)
- Everything plxjs rejects, plus type declarations (
interface,type,enum), and type-only imports. Generic annotations (Array<T>) and nested arrays (number[][]) are not mapped; useT[]or a SQL type name. - Multiple annotated declarators in one statement (
let a: number = 1, b = 2;); use one declaration per statement. - Annotations are not type-checked; they only set the plpgsql declaration type.
plxpython3 (chapter)
def,class,lambda, decorators, comprehensions, generators.import, modules, and the Python standard library.- Tuples, lists, dicts, and sets as general values.
match/case, and the conditional expressiona if c else b.- String prefixes other than
f(r,b,u). - Floor division
//;/on integers truncates toward zero. - The
\N{name}string escape; write\uor\Uwith the code point. - A negative
range()step held in a variable; write a negative step as a literal, such asrange(10, 0, -3).
plxgo (chapter)
- Goroutines and channels (
go,chan,select,<-),defer,goto. - Labelled statements (
outer:before a loop,break outer,continue outer).CREATE FUNCTIONfails, with a syntax error that does not name the label. Use a flag variable and an unlabelledbreak. - Wrap-around of integer types narrower than 64 bits, and unsigned types.
int8,int16,uint8andbytearesmallint,int32andintareinteger, and the unsigned types are signed SQL types. A conversion keeps the value and arithmetic does not wrap:uint8(300)is 300 (Go: 44),uint32(-1)is -1 (Go: 4294967295), and abyteholding 255 incremented is 256 (Go: 0). A value that leaves the SQL type’s range fails withsmallint out of rangeorinteger out of range. A left shift of asmallintdrops the bits past 16 instead, so anint8holding 3 shifted by 14 is -16384 (Go: 0). len(s)of a string counts characters, as SQLlength()does, where Go counts UTF-8 bytes:len("héllo")is 5 (Go: 6). Useoctet_length(s)for the byte count.- Nested functions and closures (
func). map,chan,struct, andinterfacetypes; declare data with SQL types or slices.switchfallthrough.make, which plx rejects; write a slice literal orarray_fill(0, []int{n}).+joins strings only when plx can see that every operand is a string: a string literal, astringlocal, atextorvarcharparameter, or a call that returns a string. With a row field, an array,jsonb,char(n)or a SQL function call among the operands,+stays SQL+; write"x" + string(r.a)orfmt.Sprintf("x%s", r.a).for i := range sover a slice; usefor _, v := range sor a counting loop.- Only a subset of
fmt/strings/math/strconvis mapped; other calls pass through and must be valid PostgreSQL functions. fmt.Sprintfrenders every operand in its SQL text form. A-flag and a width are kept, so%-8dstill pads; as in Go, a longer operand prints in full. A*width or precision takes the next argument, as in Go. An explicit argument index (%[1]d) is rejected, infmt.Printftoo. Go’s other flags and its precision field are dropped, so%.2fprints the operand in full rather than rounding it. The verbs that change an operand’s representation rather than its padding do not do so either:%x,%o,%b,%eand%qall produce the operand’s text, sofmt.Sprintf("%x", 255)yields255and notff. Convert explicitly (for exampleto_hex) where the representation matters.
plxcobol (chapter)
- Program structure beyond a single procedure body: the
IDENTIFICATION,ENVIRONMENT, andCONFIGURATIONdivisions, and named paragraphs with out-of-linePERFORM <paragraph>. - Group items (subordinate entries under an
01) inWORKING-STORAGE; declare elementary items, or use aTYPEclause for a composite type. - Fixed-format source (column areas); use free format.
- Report Writer, screen sections, and object orientation. The standard COBOL
intrinsic function library is not translated (a
FUNCTION name(...)call passes through and fails when the function runs); use SQL functions in expressions. - Truncation in
MOVEto an alphanumeric item. APIC X(n)item isvarchar(n), soMOVEof a longer string fails when it runs withvalue too long for type character varying(n), where COBOL truncates on the right. WriteMOVE LEFT(s, n) TO item. - The
||operator and::casts insideCOMPUTEand conditions; useCONCAT(...)andCAST(x AS type).||, like any character COBOL does not use (!,$,@, and others), is rejected atCREATE FUNCTIONtime outside a string literal or comment. A::cast fails when the function runs.
plxplsql (chapter)
- Packages, and package-qualified calls other than
DBMS_OUTPUT; the widerDBMS_*/UTL_*library. - Collections and associative arrays,
BULK COLLECT,FORALL, pipelined functions, records beyond%ROWTYPE. - Autonomous transactions and other pragmas.
- Oracle-only SQL (
(+),CONNECT BY,DECODE, Oracle hints); use the PostgreSQL equivalent. - Oracle spellings inside a
DBMS_OUTPUT.PUT_LINE/RAISE_APPLICATION_ERRORargument, schema-qualifiedseq.NEXTVAL, nested-DECLAREtype names, and parameterized cursors. - Oracle exception names other than
NO_DATA_FOUNDandTOO_MANY_ROWS; use the PostgreSQL condition name (division_by_zeroforZERO_DIVIDE,unique_violationforDUP_VAL_ON_INDEX). c%NOTFOUND/c%FOUNDcursor attributes; useNOT FOUND/FOUND.
plxtsql (chapter)
@@ROWCOUNT,@@ERROR, and other@@globals (except@@IDENTITY); useGET DIAGNOSTICSor an exception handler.- Table variables (
DECLARE @t TABLE) and cursors (DECLARE c CURSOR). GOTO,WAITFOR, labels.- Transaction control (
BEGIN TRAN,COMMIT,ROLLBACK). - Calling a stored procedure by name; only
EXEC('<sql>')dynamic SQL. +for string concatenation (use||orCONCAT(...)).- The range of
TINYINT. plx stores aTINYINTassmallint, so aTINYINTvariable andCONVERT(TINYINT, x)hold values outside 0 to 255:CONVERT(TINYINT, 300)is 300 andCONVERT(TINYINT, -1)is -1, where SQL Server raises an arithmetic overflow error. Check the range yourself where it matters. - T-SQL precedence for
^,&and|followed by+or-: PostgreSQL binds them more loosely, so1 ^ 2 + 3gives 4, not 6. Write(1 ^ 2) + 3. SELECT @t = @t + v FROM t, where the assignment list reads a variable it assigns; use an aggregate such asSUM(v)orstring_agg(...).- The 30-character default for a string type without a length. SQL Server
gives 30 characters to
VARCHAR,NVARCHARandCHARinCASTandCONVERT. plx gives 30 only toCAST(x AS nvarchar).CAST(x AS varchar)andCONVERT(nvarchar, x)keep every character, andCAST(x AS char)keeps 1, because PostgreSQLcharmeanschar(1). Write the length. - Keeping the writes of a
TRYblock that fails. When an error reachesCATCH, plx undoes the database changes that theTRYblock made, because plpgsql rolls an exception block back to a savepoint. SQL Server keeps them. Make a write that must survive beforeBEGIN TRYor inCATCH. See A CATCH block undoes the writes made in TRY. - Truncation in an implicit conversion to an integer type outside the function
body.
CAST,CONVERTand assignment to an integer variable truncate as in SQL Server, butRETURN 2.7from a function that returnsintgives 3, and so does anINSERTorUPDATEof 2.7 into anintcolumn. SQL Server gives- Write
CAST(e AS int). See Conversion to an integer type.
- Write
Working around a gap
Because every dialect compiles to the same plpgsql, you can write the awkward
part of a function in whichever dialect expresses it best, or drop to a SQL
statement inside any dialect (all of them pass SQL text through to query,
execute, RETURN QUERY, and the like). A construct a dialect rejects is
reported at CREATE FUNCTION time with a source line, so those gaps surface
immediately. CREATE FUNCTION then makes the checks plpgsql makes for its own
functions. It rejects pseudo-type arguments and results that plpgsql does not
allow, such as cstring or internal. When check_function_bodies is on (the
default), it also compiles the generated plpgsql, which rejects syntax errors
and trigger functions that declare arguments. This compile takes as long as
the first call’s compile would, which can be seconds for a very large
function. pg_restore turns check_function_bodies off, as it does for
plpgsql. Library calls and
expressions passed through to SQL are checked for syntax only; names and types
in them are not checked until the function runs.