Contents
Changelog
All notable changes to plx are recorded here. The format follows
Keep a Changelog, and plx uses the extension
version in plx.control (currently 3.0.0).
[3.0.0] - 2026-11-20
3.0.0 is a major release. It holds the fixes from a security audit and from a code audit of every dialect. Many fixes make a function compute what its source language computes, so a function can give a different result once you re-create it under 3.0.0. Other fixes reject code that 2.0.0 accepted. Read Breaking changes and Upgrading before you install this release.
Breaking changes
Each entry below changes what a function computes, rejects code that 2.0.0
accepted, or stops an operation that used to work. A function that already
exists keeps the plpgsql it was created with. It behaves as before until you
run its CREATE FUNCTION again (see Upgrading).
All dialects
CREATE FUNCTIONmakes plpgsql’s own checks. Every plx language now rejects result and argument types that plpgsql does not allow, such ascstringorinternal. Whencheck_function_bodiesis on, it also compiles the generated plpgsql. A body that plpgsql cannot parse, or a trigger function that declares arguments, now fails atCREATE FUNCTIONinstead of at every call. For a very large function this compile can take seconds.pg_restoreruns withcheck_function_bodiesoff, so it still restores such functions.- The extension is no longer relocatable (
relocatable = false). Functions now store plx’s schema name, soALTER EXTENSION plx SET SCHEMAwould leave them pointing at the old schema. It now fails withextension "plx" does not support SET SCHEMA. Choose the schema atCREATE EXTENSION plx SCHEMA ...time. Existing functions keep the unqualified names they were generated with until their DDL is run again. For the same reason, a function that uses a string append, or a plxtsql conversion to an integer type of a value whose type plx cannot see (which callsplx_tsql_int2,plx_tsql_int4orplx_tsql_int8), and is restored into a database where plx is in a different schema (apg_dump -nof one schema, say) fails when it runs: withschema "..." does not existwhen that schema is missing in the target, and otherwise withtype "public.plx_strbuild" does not existorfunction public.plx_tsql_int4(record) does not exist(with the source schema’s name in place ofpublic). Install plx in the same schema in the target, or run the function’s DDL again after restoring. Other functions are not affected. CREATE FUNCTIONandDOin a plx language need USAGE on plpgsql. A role without that privilege now getspermission denied for language plpgsql. See “Revoking plpgsql now revokes plx” under Security.- A declared type must be a type name. A type annotation or declared type
that is not a type name,
name%TYPEorname%ROWTYPEis now rejected atCREATE FUNCTION. See “A type annotation could run code on entry” under Security.
Several dialects
Each entry names the dialects it affects.
- An interpolated value in a SQL argument is bound, never spliced as SQL.
In
query,fetch_one,execute,open_cursor,return_queryandperform, an interpolation in the SQL string (#{x},{$x},${x}, f-string{x}) used to be concatenated into dynamic SQL unquoted, so data could inject SQL. Adding a bind argument to a safe static call silently made it injectable. Now a value becomes a$nparameter passed withUSING(after the call’s own bind arguments). In a statement that takes no parameters (CREATE,ALTER,SET,COMMENT, …), the value is spliced throughquote_nullable().'#{x}'is the value ofxin static and dynamic SQL alike (it used to be the literal text'x'in static SQL), and an interpolation inside a longer literal concatenates the value.'#{x}'now has the type ofx; before, it was an untyped literal that took the type its context needed. So with atextvariable,ts > '#{since}'on adatecolumn now fails withoperator does not exist: date > text; write'#{since}'::date. A literal after a built-in type name, such asinterval '#{n} days', becomesCAST(('' || (n)::text || ' days') AS interval), andinterval '#{s}'becomesCAST((s)::text AS interval). An interval field after such a literal,interval '#{n}' hour, is rejected: writeinterval '#{n} hours'. An interpolation inside a quoted identifier, a SQL comment, a dollar-quoted string or a prefixed literal (U&'...',N'...',text'...') is rejected, and so is one joined to a SQL name or$sign (v#{x}), and one right after an unpaired backslash in anE'...'string (the SQL textE'a\#{x}', writtenE'a\\#{x}'in a dialect string), where PostgreSQL would read the quote plx writes next as an escaped quote. The lowering reads the SQL string as PostgreSQL does, so a quote inside a comment or a dollar-quoted string does not open a string. A literal that contains a backslash is written asE'...'in dynamic SQL too, so the SQL reads the same whateverstandard_conforming_stringsthe caller uses. A value interpolated where SQL needs a name (FROM #{t}) is now an error until the body usesident()orraw()(see Added). In static SQL the value is written in parentheses,FROM (t), soCREATE FUNCTIONfails with a syntax error; before, it read the table namedtwhatever the variable held. One static position is not caught:UPDATE t SET #{c} = ...becomesSET (c) = ..., a one-column list: with a sub-SELECTon the right it still updates the column namedc, as 2.0.1 did; see doc/LIMITATIONS.md. In dynamic SQL the statement fails when it runs, withsyntax error at or near "$n". A value interpolated to build SQL text on purpose is now bound as data. See doc/DYNAMIC-SQL.md. - An expression interpolated in static SQL is one value (plxruby,
plxphp, plxjs, plxts, plxpython3). In a static call, an expression interpolated outside quotes is now written in
parentheses, so the SQL around it cannot split it, as in dynamic SQL, which
binds the whole value. 2.0.1 wrote it bare:
SELECT ${a + b} * 10(plxjs, and#{a + b} * 10in plxruby) ran asSELECT a + b * 10and gave 21 fora = 1, b = 2. It now runs asSELECT (a + b) * 10and gives 30. In the same way, plxpython3f"SELECT {a or b} AND false"gave true fora = true, b = falseand now gives false. finally/ensurenow runs onreturn,breakandcontinue(plxjs, plxts, plxphp, plxruby, plxpython3), as in the source languages. Before, areturninsidetryleft the function without runningfinally. The finally body is now emitted once. An exit from the try (or from a catch arm) records which exit is pending, leaves the block, runs the finally body, then completes the exit. An error is caught, the finally body runs, and the error is re-raised with its SQLSTATE, message, detail, hint and object names (schema, table, column, constraint, type). Areturnor an error in the finally body replaces the pending exit or error, as in the source languages. Two differences remain. An error re-raised afterfinallycarries the location of the re-raise in itsCONTEXT, not the original statement’s. When it names any object, the object names it does not have are sent as empty fields rather than left out (psql shows them in verbose mode). As before, the finally body does not run when the statement is cancelled (a query cancel orstatement_timeout) or anassertfails:WHEN OTHERSdoes not catch those two errors.- Appending a NULL to a string makes the string NULL (plxruby, plxphp,
plxjs, plxts, plxpython3).
s << x,$s .= $xands += xon a string now give the same result ass := s || x. Ifxis NULL, orsis already NULL,sbecomes NULL and stays NULL until you assign it again. Before, a NULL value was skipped and a NULL string started again from empty, so$s .= "id:{$x} "could drop a whole segment without any sign. In plxphp, a variable whose first use is.=starts as an empty string, as in PHP. plxcobolSTRING-APPENDdoes not change. To treat a NULL as empty, appendcoalesce(x, ''). A message built with appends and then raised now shows<NULL>if any part was NULL. - An append keeps a typed local’s type (plxruby, plxphp, plxjs, plxts,
plxpython3). An append (
<<,.=,+=) no longer turns a local declaredvarchar(n),char(n),citextor an array into a string builder. Such a local keeps its type, so a length limit is enforced, and an append to avarchar(n)local can now raisevalue too long. A NULL appended to such a local makes it NULL, as SQL||does. Ruby<<on an array appends an element, and PHP.=on an array is rejected atCREATE FUNCTION. - A
switchwith no default arm does nothing when no arm matches. This coversswitch(plxjs, plxts, plxphp),case(plxruby) andEVALUATE(plxcobol). Before, it raisedcase not found, because a plpgsqlCASEstatement withoutELSEdoes. It now does nothing, as in the source language. plxplsql keeps the error, which is also Oracle’s behaviour. - An interpolated string as a local’s first value runs in place. The first
top-level assignment of a string literal is folded into the
DECLAREsection as the variable’s initializer, which runs when the function starts. An interpolating string (Ruby"#{v}", PHP"$v", JS`${v}`, Pythonf"{v}") was folded too, so it read its variables before the statements above it had run:v = "v0"; v = "v2"; s = "got #{v}"returnedgot v0. Such an assignment now runs in place. A plain literal is still folded. - A constant may not read a local variable. A constant becomes a plpgsql
CONSTANT, computed when the function starts, sov = 1; v = 2; c = v + 1 #:: int constgavec = 2. A constant may now use only literals, parameters and earlier constants. Reading another local is rejected atCREATE FUNCTION. - A block annotation before an initializer is rejected (plxjs, plxphp).
let n /*:: bigint */ = 3;silently dropped the initializer, sonwas NULL. That form is now rejected with a hint to writelet n = 3 /*:: bigint */;. - Names that differ only in case are rejected (plxruby, plxphp, plxjs,
plxts, plxpython3, plxgo).
CREATE FUNCTIONnow rejects a body that spells one local or parameter two ways (aandA, orNbeside parametern). plx used to merge them into one plpgsql variable, so a second declaration overwrote the first and an assignment toNoverwrote parametern. A counting-loop variable that spells an earlier local or parameter another way, such asfor (let I = ...)besidelet i, is rejected too; before, it hid the local. Rename one of the names. - Nested loop labels that differ only in case are rejected (plxjs, plxts,
plxphp, plxpython3, plxruby). plpgsql ignores case in labels too, so in
Outer: for ... { outer: for ... { continue Outer; } }thecontinuewent to the inner loop and the function returned a wrong value.CREATE FUNCTIONnow rejects a loop label that spells the label of an enclosing loop another way. Rename one of the labels. - An escaped interpolation in SQL text is plain text (plxphp, plxjs,
plxts, plxruby). In the SQL argument of
query,fetch_oneand the other SQL calls,\$x,\${x}and\#{x}put a backslash and the value ofxinto the SQL, so plxphpfetch_one("SELECT '\$x' AS v")returned\5where$xheld 5. They now put the text$x,${x}or#{x}there, as in a string value. A plxpython3 f-string is unchanged:\{x}keeps the backslash and still interpolates, as in Python. plxphp"\{$x}"keeps the backslash and the braces and interpolates$x, as PHP does: with$x5 it gives\{5}, in a string value and in SQL. Before, it gave{5}in both. - A quote or brace inside an interpolation does not end it (plxruby,
plxpython3, plxjs, plxts, plxphp). plx ended an interpolated expression at
the first
}, even inside a string in the expression, so plxpython3f"{'}'}"gave'}instead of}. It now gives}. Other forms that failed now work: the Ruby idiom"#{s == "a" ? "A" : "other"}", Pythonf"{concat('}', x)}", and?,:and**inside a string nested in an interpolation, such as Ruby"[#{"a?b:c"}]", which are now text. - An integer literal with a leading zero is octal (plxruby, plxphp,
plxjs), as in the source language, so
0755is 493, not 755. plxpython3 and plxts reject such a literal atCREATE FUNCTIONand suggest0o755. ^is bitwise XOR, and**binds as in the source language (plxruby, plxphp, plxjs, plxts, plxpython3).^now becomes SQL#instead of SQL exponent, so6 ^ 3is 5, not 216.**is now right-associative and binds tighter than unary minus, so2 ** 3 ** 2is 512 (was 64) and-2 ** 2is -4 (was 4). A mix of bitwise operators that SQL would group differently from the dialect, such asa ^ b & cora | b & c, is now rejected atCREATE FUNCTIONwith a request for parentheses; it used to compute a different value. A mix that SQL groups the same way, such asa & b | c, still works.- A chained assignment is rejected (plxruby, plxphp, plxjs, plxts,
plxpython3).
a = b = 0used to compile toa := b = 0, leavingbunchanged and givingaa boolean. It is now rejected atCREATE FUNCTION, and so is a chain with a compound operator such asa = b <<= 2,a = b &= 3ora = b ??= 4, which used to fail only when the function ran. - String escapes keep their meaning (plxruby, plxphp, plxjs, plxts,
plxpython3).
"\x41"isAand"\v"is a vertical tab; they used to lose the backslash and become literal letters. A NUL character (\0) is now rejected atCREATE FUNCTION, because text cannot hold it. In plxphp and plxpython3 an unknown escape such as\qkeeps its backslash, as in those languages. - Adjacent string literals are concatenated (plxruby, plxpython3).
"a" "b"isab. They used to merge into one SQL literal with a quote (a'b), and an escape string next to one ending in a backslash could swallow the code after it. A plxpython3 triple-quoted string is now one literal, so"""hello"""ishello, not'hello', and quotes inside it are text. - A comment inside an expression is left out of the SQL (plxphp, plxjs,
plxts, plxpython3, plxruby). A comment inside a multi-line expression used to
be copied into the generated SQL. PHP
$a # notebecame SQL XOR and returned a wrong value, a JS//comment containing--hid the rest of the line, and an apostrophe in a comment opened a SQL string. A re-created PHP function with a#comment inside an expression now computes the PHP result. int(x)andx.to_itruncate toward zero (plxpython3, plxruby), as Python and Ruby do:int(2.7)is 2 andint(-2.5)is -2. Before, they rounded, which is what a SQL cast to integer does. Ruby"4.7".to_inow gives 4 instead of an error. In plxpython3,int()of a boolean that plx cannot see is boolean, such as a function returning boolean, now fails withcannot cast type boolean to numeric; writeint(f(x) == True)instead. In plxruby,.to_i,.to_fand.to_son an interpolated string, such as"#{a}5".to_i, now convert the whole string. Before, they converted only its last literal piece, so"#{a}5".to_i * 2gave 110 fora = 1instead of 30.- A range loop variable keeps its last value (plxpython3, plxruby).
plxpython3
for i in range(...)and plxrubyfor i in lo..hinow leave the loop variable at its last value after the loop or after abreak, as Python and Ruby do. Before, the variable kept the value it had before the loop. - A
catchclass catches only its own condition (plxphp, plxjs, plxts). Before, any class plx did not know (for examplePG::SerializationFailure,DivisionByZeroError, a typo, orA | B) caught every error, and in plxphp so did a catch with no variable, such ascatch (PG::SerializationFailure). NowPG::CamelCasemaps to the plpgsql conditioncamel_case,A | Bcatches both, andException,ThrowableandErrorstill catch everything. Any other class, with or without a variable, is rejected atCREATE FUNCTION. breakinside anifortrywithin aswitcharm leaves the switch (plxjs, plxts, plxphp), as in JavaScript and PHP. Before, inside a loop it left the loop (a wrong result with no error), and outside a loop it was rejected withbreak outside a loop.- A C-style
forthat is not a plain counting loop is rejected (plxjs, plxts, plxphp), with a hint to usewhile. Before, it was lowered to an integerFORthat ignored a condition on another variable, ignored changes the body made to the bound or loop variable, and lost the loop variable’s final value. Also rejected now: a countingforwhose variable an enclosing loop reads again before thefor(in its condition, or earlier in its body), and in plxjs and plxts afor (var i ...)whose variable is read after the loop. A read inside a string interpolation counts, as in${i}or"stopped at $i", including the SQL text ofqueryandfetch_one. An escaped interpolation such as\$ior\${i}is text, not a read, and neither is a field of the same name, such as${r.i}. Plain counting loops compile as before. throwof an expression that is not a constructor call is rejected (plxjs, plxts, plxphp), for examplethrow a(x) + b(y). Before, it silently raised only the first call’s first argument.
plxphp
- Properties and elements interpolate.
"$row->name","$a[1]","$a[$i]"and"$row[name]"inside a double-quoted string interpolate the property or element, as in PHP. Before, plx spliced the whole variable and left->nameor[1]as literal text. break Nandcontinue Nreach the Nth enclosing loop orswitch, as in PHP. Before, the level was ignored and only the innermost loop was left. Abreak Nthat leaves aswitchor a labelled loop inside two or moretry/finallyblocks runs everyfinallyit leaves. A level of 0, a level above the nesting depth, and acontinue Nthat lands on a switch are rejected.continueorcontinue 1inside a switch arm is rejected too: PHP runs it asbreak, so writebreak, orcontinue 2to continue the loop. plxjs and plxts reject a number afterbreakorcontinue.
plxruby
each_with_index do |v, i|over an array or a range sets the index, counting from 0. Before, the index was never set, so the block read the variable’s old value or failed when called. The index exists only inside its block, as in Ruby: a parameter or variable with the same name is no longer hidden for the whole function or overwritten by the loop, and a read of the index after the loop gets the outer variable, or fails if there is none. Using the same name for the value and the index (|v, v|) is an error, and so iseach do |v, i|with two block variables.
plxpython3
exceptwith an unknown class is rejected. A class plx does not know (such asKeyError, or a misspeltPG::name) now fails atCREATE FUNCTIONinstead of silently catching every error. A tuple such asexcept (PG::UniqueViolation, PG::CheckViolation)now catches only those conditions; before, it caught every error.- String prefixes other than
fare rejected.r"...",b"...",u"..."andrf"..."now fail atCREATE FUNCTION. Before,r"..."failed only when called.
plxgo
var x Tstarts at Go’s zero value (0,"",false) instead of NULL, and avarinside a loop body is reset on every iteration.var e [N]Tstarts as N zero elements (var e [3]intis{0,0,0}), not NULL.- A three-clause
forloop keeps Go’s meaning. Such loops now lower toWHILEinstead of an integerFOR. The condition is re-tested on every pass, the body may change the loop variable,for i = ...updates an existing variable, a float loop variable stays a float, andcontinueruns the post statement in every three-clause loop (before, it could loop forever).for i := i; ...starts at the outeri. A loop whose start value is a function call (for i := f(); ...) or a row field is now rejected; declare the variable first. A start value from an element of a slice declared in the body (for i := s[0]; ...) is accepted. - A
switchrunsdefaultonly when no case matches, whereverdefaultstands. A leadingdefaultused to win always, and a middledefaultfailed with a syntax error.breakinside a switch leaves the switch instead of the enclosing loop. - A tagged
switchevaluates its tag once and compares each case value as a whole, soswitch nextval(...)advances the sequence once andcase a || b:means tag == (a || b). - Bitwise operators keep Go’s meaning and precedence.
^is XOR (it was SQL power, so6 ^ 3gave 216), unary^xand&^work,1 << 2 + 1is 5, and<<=,>>=,&=,|=,^=and&^=are accepted. Unary^xbinds tighter than+and*, so^y + 1gives-y. - An integer constant shifted by
<<or>>is a 64-bit value. SQL typed the literal as a 32-bit integer and took the shift count mod 32, sovar m int64 = 1 << ngave -2147483648 for n = 31 and 1 for n = 32, and1 << 40gave 256. plx now shifts the constant asbigint, which gives Go’s values. This includes a named constant, a constant expression or a rune literal, such asmask << 28afterconst mask = 0xFF,(1) << 40,2 * 1 << 40and'a' << 40. Stored in anint(a 32-bitintegerin plx), a result that does not fit now fails withinteger out of rangeinstead of wrapping. - A shift count at least as large as the width of the value gives Go’s
result. SQL took the count mod 64 for a
bigintand mod 32 for aninteger, so withn= 64,1 << ngave 1 andx >> ngavex. Go gives 0 for<<, and 0 or -1 for>>, and so does plx now. A negative count fails, as it panics in Go. An expression with more than 64 shifts by a count that is not a literal, nested in one another, is rejected atCREATE FUNCTION. - An
intshifted left out of the range of a 32-bit integer fails withinteger out of range, whatever the count. plx mapsinttointeger, and SQL dropped the high bits:x << 30withx3 gave -1073741824 (Go’s 64-bitintgives 3221225472). Declare the variableint64to get Go’s value. - Integer conversions truncate.
int(x),int64(x)and the other integer conversions truncate a float toward zero, as Go does, instead of rounding (int(3.7)is now 3).int(x)andint64(x)of a row field or an expression stay exact for integers beyond 253, andint(r.n)of a text field works. - A
*width or precision infmt.Sprintftakes the next argument, as in Go. plx printed%*das text and gave every later verb the wrong argument, sofmt.Sprintf("[%*d|%s]", 5, 42, "x")gave[%*d|5]; it now gives[ 42|x]. A*precision takes its argument and is then dropped, like a written precision. Where the directives and arguments do not line up, a*is rejected atCREATE FUNCTION. - A
fmt.Sprintfwidth pads but never truncates.%3dof 12345 gave"123"; it now gives the full value. An explicit argument index such as%[1]dis rejected atCREATE FUNCTION, infmt.Sprintfandfmt.Printf, instead of producing garbled text or printing the values in the wrong order. - Literals keep their value. String and rune literals decode every Go
escape (
\x, octal,\u,\U,\a\b\f\v). A rune literal such as'\xe9'or'\351'is the code point 233 (see the next entry). An unknown escape (such as"\d"), a NUL, or bytes that are not valid text are rejected atCREATE FUNCTION. Hexadecimal, octal (0oand leading0), binary and_-separated integer literals keep their value on every supported PostgreSQL version (017was 17,0o17was 0, and on PostgreSQL 13 to 150x1Fwas 0). A number with letters glued to it is rejected. - A rune literal is an integer, its code point, as in Go. plx emitted it
as a SQL string, so
var d rune = '7'stored 7 instead of 55,var r rune = 'a'failed withinvalid input syntax for type integer, and'7' - '0'failed withoperator is not unique.c := 'a'now declares aninteger. A rune literal must hold exactly one character.string(x)of an integer is now the character with that code point, as in Go, instead of the number’s digits:string(65)is"A", not"65". This includes the variable of afor i := 1; ...; i++loop. Usestrconv.Itoafor the digits.%cinfmt.Sprintfprints the character.string(x)of a float,numericor bool value gave its text and is now rejected atCREATE FUNCTION, as Go rejects it. Format such a value withfmt.Sprintf. +between a string and a number is rejected atCREATE FUNCTION, as Go rejects it. Before,n + "1"with an integernreturnedn + 1, because SQL read the literal as a number. Convert the number withstrconv.Itoaorfmt.Sprintf.strconv.Itoa(x)gives a string. It was lowered to(x)::integer, so its result compared and sorted as a number:strconv.Itoa(10) < strconv.Itoa(9)gave false. It is now(x)::text, so that comparison gives true, as in Go. Assigning the result to astringvariable gives the same text as before.
plxcobol
PERFORM VARYING ... BY 1 UNTIL v > bno longer hides its control variable. After the loop, and afterEXIT PERFORM, the variable holds the COBOL value (for example N+1, not its old value). AMOVEto the variable or to the bound inside the body now affects the loop, and aPIC 9(18)control variable no longer fails withinteger out of range.- A character that COBOL does not use is rejected.
!,$,@or|outside a string literal or comment now fails atCREATE FUNCTION. Before, it was silently dropped, soA != Bcompiled toa = b,$1to the literal 1, and@ Atoa. Bodies that used||now fail to create; useCONCAT(...). - Stacked
WHENphrases share their statements. InEVALUATE TRUE, and for aWHENstacked directly beforeWHEN OTHER, the stacked phrases now share the statements that follow, as in COBOL. Before, the earlierWHENgot an empty arm, so a matching value did nothing. - A store into a numeric PICTURE item truncates.
MOVE,COMPUTE,ADD,SUBTRACT,MULTIPLY,DIVIDE, thePERFORM VARYINGstep and a decimalVALUEclause now truncate the excess fractional digits, as COBOL does.COMPUTE WS-I = 7.9stores 7, not 8; 2.0 / 3 inPIC 9(3)V99stores 0.66, not 0.67;PIC 9(4) VALUE 2.5starts at 2, not 3.DIVIDEinto a numeric PICTURE item keeps the quotient’s fraction, soDIVIDE 2 INTO 7 GIVINGaPIC 9V99item stores 3.50, not 3.00. The/operator inCOMPUTEstill follows SQL integer division. WriteROUNDEDafter the receiving field to round half away from zero (DIVIDE 4 INTO 3 ROUNDEDstores 1); it was rejected before.ROUNDED MODEis not supported. DISPLAYshows a table element and rejects other expressions.DISPLAY WS-ARR(2)now shows element 2, not the whole array. ADISPLAYoperand that is not a data name, table element or literal (such as a function call) is now rejected atCREATE FUNCTIONinstead of being silently dropped.DIVIDE a BY bwithoutGIVINGis rejected atCREATE FUNCTION, as COBOL requires. Before, it overwrote the divisorbwitha / b.
plxtsql
SELECT @v = expr FROM ...assigns once per row, as in SQL Server. The variable ends with the last row’s value and keeps its old value when no row matches. Before, it took the first row and became NULL when no row matched. An assignment list that reads a variable it assigns (the accumulate idiomSELECT @t = @t + v FROM t) is now rejected atCREATE FUNCTION, because plx cannot compute it the way SQL Server does; useSUM()orstring_agg(). The error names the entry that reads the variable. A list of more than 64 assignments is no longer split, and an entry with no=is now an error instead of emitting invalid code.[bracketed]and"quoted"identifiers keep their quotes. They become PostgreSQL quoted identifiers, so[user]reads the columnuserinstead of returningcurrent_user. A[bracketed]identifier is folded to lower case and then quoted, so[Orders]still matches a table created asorders, and[Order Details]works. A"double-quoted"identifier keeps its case, so"Name"reads the column"Name"instead ofname. An unterminated[or"is now an error atCREATE FUNCTION.^is bitwise exclusive OR andLEN()ignores trailing spaces, as in SQL Server.a ^ bnow becomesa # b. Before, it became PostgreSQL exponentiation, so5 ^ 3gave 125 instead of 6.LEN(x)now becomeslength(rtrim(x)), soLEN('abc ')gives 3 instead of 6. T-SQL^,&and|bind as tightly as+and-, but PostgreSQL binds#,&and|more loosely, so1 ^ 2 + 3gives 4 in plx, not 6. Write(1 ^ 2) + 3.- Unary
~applies to its operand only, as in SQL Server, where it binds more tightly than any other operator. plx now writes~aas(~a). Before, it passed~through, and PostgreSQL binds it more loosely than+and*, so~1 + 2computed~(1 + 2)and gave -4. It now gives 0. RAISERRORwith severity 10 or lower prints a notice and continues. It used to raise an error, so the progress-message idiomRAISERROR('step', 0, 1) WITH NOWAITstopped the function on its first run. As in SQL Server, a severity of 10 or lower now givesRAISE NOTICE, and aCATCHblock does not catch it. A severity of 11 or higher, -1 orERROR_SEVERITY()still raises an error. A variable severity is checked at run time, and a NULL severity raises an error. A trailingWITH NOWAIT,WITH SETERRORorWITH LOGused to be emitted as a separate statement, which failed at run time withsyntax error at end of inputwhenever the error did not fire. These options are now accepted, andWITH LOGalso writes the message to the server log. An unknown option is rejected atCREATE FUNCTION. A function that used a severity of 10 or lower to stop on purpose now continues; to keep the old behaviour, use severity 16 orTHROW.CASTtoBITgives abooleanandCASTtoMONEYgivesnumeric(19,4), asDECLAREandCONVERTalready did. Before,CASTpassed the name to PostgreSQL, whosebitkeeps only the lowest bit, soCAST(2 AS bit)gave 0 instead of 1. PostgreSQL’smoneyturned into text such as$2.50. Abooleanhas its own text forms: a cast to text givestrueorfalse, andCONCATgivestorf. SoCONCAT('a', CAST(1 AS bit))now givesat; before, and in SQL Server, it gavea1.CASTtoMONEYnow rejects a string with a currency sign:CAST('$5' AS money)fails withinvalid input syntax for type numeric. Before, it gave$5.00, and SQL Server gives 5.00. Remove the sign before the cast.- A conversion to an integer type truncates, and
DECIMALwith no precision isdecimal(18,0), as in SQL Server.CASTandCONVERTtoINT,INTEGER,BIGINTorSMALLINT(andCONVERTtoTINYINT), and an assignment to a variable of one of these types, now truncate a fraction toward zero. Before, they rounded:CAST(1.5 AS int)gave 2 instead of 1,CONVERT(int, -2.5)gave -3 instead of -2, andDECLARE @i int = 2.7gave 3 instead of 2. A bareDECIMAL,NUMERICorDECinDECLARE,CASTorCONVERTnow becomesnumeric(18,0). Before, it was PostgreSQLnumeric, which keeps every digit:DECLARE @d DECIMAL = 2.7gave 2.7 instead of 3.RETURNandINSERTinto an integer column still round; see doc/LIMITATIONS.md. For a value whose type plx cannot see, such as a column or a function call, the generated code calls the new functionsplx_tsql_int2,plx_tsql_int4andplx_tsql_int8, whichALTER EXTENSION plx UPDATE TO '3.0.0'installs. A value of a domain overnumeric,realordouble precision, such as a T-SQL alias type, truncates too.
plxplsql
SELECT ... INTOandEXECUTE IMMEDIATE ... INTOraiseNO_DATA_FOUNDandTOO_MANY_ROWS, as in Oracle. plx now emitsINTO STRICT. Before, a query with no row set the target to NULL, and a query with more than one row kept the first row, so the Oracle exception handlers never ran. A function that relied on the old NULL or first-row result now raises an error. plx does not addSTRICTtoWITH ... INSERT INTO, toWITH ... UPDATE/DELETE ... RETURNING ... INTO, or to anINTOthat already hasSTRICT.
Upgrading
These steps are for a database administrator who upgrades plx from 2.0.0 to 3.0.0. You need superuser access to the server and to each database that has the plx extension.
- Install the new build on the server.
Restart the server, or start new sessions, so that they load the new build. The transpiler fixes need only this step.
Caution: Run step 3 in each database right after this step, before anyone creates or replaces a plx function there. Until step 3 runs, a function that uses a string append (Ruby
<<, PHP.=, or+=on a string in plxjs, plxts or plxpython3) is created without an error, but every call to it fails. So does a plxtsql function orDOblock that converts a value whose type plx cannot see, such as a column or a function call, to an integer type (CAST,CONVERT, or an assignment to anINT,BIGINT,SMALLINTorTINYINTvariable):
ERROR: function public.plx_sb_append_strict(plx_strbuild, text) does not exist
or, for plxtsql,
ERROR: function public.plx_tsql_int4(record) does not exist
The schema in the message is the one plx is installed in. The new build
generates calls to these functions, and only step 3 creates them. After step
3, the function works without being created again. Until step 3,
ALTER EXTENSION plx SET SCHEMA is also still allowed, and it would break
the functions the new build creates.
3. Right after step 2, in each database that has plx, run:
ALTER EXTENSION plx UPDATE;
The update records the extension as non-relocatable. It adds binary send
and receive functions to plx_strbuild and makes it collatable. It gives
the collation to every type built on plx_strbuild (its array type,
domains, domains over those domains, and their array types) and to
existing columns, range types, index keys and partition keys of all of
these, as a fresh install does. It also adds the functions that string
appends and plxtsql integer conversions call in 3.0.0. An expression
stored before the update keeps no collation until you re-create the
object that holds it: a view’s query, a generated column, a column
DEFAULT, a CHECK constraint, a row security policy, and the body of a
SQL-standard function (BEGIN ATOMIC). Until then, an operation that
sorts plx_strbuild values from it can fail with
could not determine which collation to use for string comparison:
ORDER BY, DISTINCT ON, PARTITION BY, GREATEST and LEAST, and
GROUP BY or DISTINCT when the planner sorts. Comparison operators such
as < and =, min() and max() still work.
To check the result, run
SELECT extversion, extrelocatable FROM pg_extension WHERE extname = 'plx';.
It returns 3.0.0 and f.
4. List the plx functions in the database:
SELECT p.oid::regprocedure AS function, l.lanname AS language
FROM pg_proc p
JOIN pg_language l ON l.oid = p.prolang
WHERE l.lanname LIKE 'plx%'
ORDER BY 1;
Each of these keeps the plpgsql it was created with, so it behaves as it did
under 2.0.0 until you run its CREATE FUNCTION again.
5. Find the functions that a breaking change affects. plx has no query that
does this exactly. Read each function’s source with the plx_source()
helper in doc/DEBUGGING.md, and compare it with Breaking changes. To narrow
the list, search the source for the construct that a change concerns, for
example ^:
SELECT p.oid::regprocedure AS function
FROM pg_proc p
JOIN pg_language l ON l.oid = p.prolang
WHERE l.lanname LIKE 'plx%'
AND plx_source(p.oid) LIKE '%^%';
The search matches text, so it also finds the construct inside strings and
comments.
6. Re-create each function from its source with CREATE OR REPLACE FUNCTION,
then test it. The function then uses the 3.0.0 code.
Security
Every fix in this section concerns input to CREATE FUNCTION or DO, which
any role with USAGE on a plx language can send (the languages are trusted, so
PUBLIC has USAGE by default).
- Backend crash on a type annotation at the start of an expression. A
/*:: T */or#:: Tannotation as the first token of anif/whilecondition, a call argument, or a statement made the transpiler copy a span from a NULL pointer, killing the backend with SIGSEGV (and resetting every session on the server). Affected plxjs, plxts, plxphp, plxruby, plxpython3, including DO blocks. Annotations are now skipped at span edges. - Backend crash on a loop over
query()with no arguments (plxjs, plxts, plxphp, plxpython3).CREATE FUNCTIONorDOnow fails withquery requires a SQL argument. - Stack-overflow crash on deeply nested expressions in plxtsql, plxgo and
plxcobol. Their expression emitters recursed once per nested call,
subscript or literal without
check_stack_depth(), so a body likeISNULL(ISNULL(...))orint(int(...))some 50,000 levels deep crashed the backend. They now raiseexpression nested too deeply, like the shared rewriter already did. - Exponential output from nested
finally/ensure. Each finally body was emitted once per exit path, so a finally nested inside a finally doubled the output per level: a 668-byte function stored a 94 MB body, and a slightly deeper one drove a backend to gigabytes of memory. The finally body is now emitted once (see Breaking changes), so output grows linearly with nesting. - String literal escaping its quotes. A dialect string that ended in a lone
backslash, inside an interpolation (plxjs/plxts
${'...\'}, plxpython3 f-strings), was emitted with that backslash bare inside anE'...'literal, where it escaped the closing quote. The next string literal in the body then ran as code, so a function whose source showed only inert string assignments could execute arbitrary SQL. - String literals that depend on
standard_conforming_strings. plpgsql reads a function body with the caller’s setting, which any role may change. Literals that plxgo, plxtsql, plxplsql and plxcobol emit, and Ruby and PHP single-quoted strings, were written as plain'...', so with the setting off a literal ending in a backslash swallowed its closing quote and the following text ran as code; the caller could change what aSECURITY DEFINERfunction did. The finished body is now rewritten so that every ordinary literal containing a backslash is anE'...'literal with the backslash doubled, which reads the same under either setting and, with it on, has the value the original had. This covers every dialect, and SQL passed through verbatim. A typed literal (text'C:\') becomestext E'C:\\', and anN'...'literal becomesNCHAR E'...', the form PostgreSQL readsN'...'as. A--comment ends at a carriage return as well as at a newline, as in PostgreSQL, so a literal after one is rewritten too. - Quadratic, uninterruptible transpile time. Local-variable lookup was a
linear scan, so a body with 40,000 distinct variables took 47 seconds; the
plxpython3 lexer rescanned every run of blank lines from each newline, so
400,000 blank lines took 43 seconds. Neither honoured
statement_timeout. Locals are now hashed (keyed as PostgreSQL reads an identifier, down-cased and truncated to 63 bytes, so no name defeats the index), blank and annotation lines are scanned once, and the lexer, rewriter and local table check for interrupts. (The same fix stops plxpython3 from counting blank lines twice in error line numbers.) - Quadratic, uninterruptible transpile time in plxtsql and plxplsql. A
plxtsql body with many unbalanced call parentheses (
f(f(f(...), or a plxplsql body with many unbalancedDBMS_OUTPUT.PUT_LINE(orRAISE_APPLICATION_ERROR(calls, took quadratic time to transpile and could not be interrupted, so any role could tie up a backend. So did deeply nestedCONVERT,IIF,CHARINDEXorRAISE_APPLICATION_ERRORcalls with no comma, and deeply nestedCAST(... AS type). These now transpile in linear time, andstatement_timeoutcan cancel the transpile. - Quadratic, uninterruptible transpile time for nested brackets. In
plxjs, plxts, plxphp, plxpython3 and plxruby, the rewriter for
**scanned each bracketed group to its end at every(or[, so deeply nested brackets took quadratic time: 60,000 levels of("x" + ...)took 44 seconds, andstatement_timeoutcould not cancel it. The rewriter now finds every group in one pass, which takes 33 milliseconds for the same body, and checks for interrupts as it goes. - Quadratic, uninterruptible transpile time and a crash in plxgo string
+. To decide whether a+joins strings, plxgo typed each bracketed operand again at every level of nesting. A body of nested("x" + (...))therefore took quadratic time and memory: 6,400 levels took about 40 seconds, and 40,000 levels used 12 GB and ignored bothpg_cancel_backendandpg_terminate_backend. A string+inside the arguments of nested calls, such asstrings.ReplaceAll(x, "a", "a" + strings.ReplaceAll(...)), was emitted twice per level, so the time doubled with each level. A chain of 200,000 unary signs (- - - 1) overflowed the stack and crashed the backend. plxgo now types each bracketed expression once, stops at the nesting limit before it does the deeper work, types unary signs without recursion, and checks for interrupts while it lexes, types and emits, sostatement_timeoutcan cancel the transpile. - Exponential memory when plxgo subqueries are nested. plxgo evaluates
some values once in a subquery: the operand of
int64(x)orint(x)when plx cannot see its type, the value of afmt.Sprintfwidth when it is a call, and the value and width of a*width. The planner pulled each subquery up and copied the value into every place it is used, so the plan grew exponentially with nesting: 8 nestedint64()conversions ran out of memory when the function ran. The subqueries now haveOFFSET 0, which keeps the planner from pulling them up. - Memory amplification in chained ternaries. Each
?:level kept its own copy of the rest of the chain, so peak memory was depth times source length (568 MB for a 1 MB body). Intermediate results are now freed. - plxgo
fmt.Sprintfdirective without a letter verb. A%followed by a quote consumed half of the escaped''pair, so text inside a Go string literal could end up as SQL in the generated body. Such directives now fall back to theformat()lowering, which keeps the text literal. - Revoking plpgsql now revokes plx. A plx function is stored and run as
plpgsql, but a role that kept USAGE on a plx language could still create
plpgsql functions and run DO blocks after
REVOKE USAGE ON LANGUAGE plpgsql FROM PUBLIC(for example withDO LANGUAGE plxplsql, or a body that already carried the transpiled header).CREATE FUNCTIONandDOin a plx language now also require USAGE on plpgsql, failing withpermission denied for language plpgsql. As with plpgsql, functions that already exist keep running, and superusers are unaffected. - Generated code names plx’s own objects with plx’s schema. A string
append (
s << x,$s .= x,s += x,STRING-APPEND) declared the variable asplx_strbuildand called the append function unqualified, so they were resolved throughsearch_pathevery time the function ran. That broke the standard hardening for aSECURITY DEFINERfunction (SET search_path = pg_catalog:type "plx_strbuild" does not exist), broke functions when plx was installed in a schema not on the path, and let a same-named function planted earlier on the path run in their place, with the definer’s privileges for aSECURITY DEFINERfunction. Generated code now writes<plx schema>.plx_strbuildand qualifies the append function with the same schema. - A type annotation could run code on entry. A local variable’s type is
spliced into the plpgsql
DECLAREsection, and plpgsql reads a declaration up to its;. A type annotation could therefore carry an initializer or more statements. For example,x #:: int := f()orlet x /*:: int DEFAULT f() */;ranf()on entry, while the source showed only a type, and in plxjs only a comment. Every type that reachesDECLARE(annotations, plxts types, plxgo and plxtsql declarations, the plxcobolTYPEclause) must now be a type name accepted by the SQL type-name grammar, orname%TYPE/name%ROWTYPE. Anything else is rejected atCREATE FUNCTION.
Fixed
Fixes that change a result or reject code are listed under Breaking changes, not here.
All dialects
plx_strbuildworks in sorting, grouping and binary transfer. The type now has binary send and receive functions and is collatable like text. BinaryCOPY, binary protocol results,ORDER BY,DISTINCT,GROUP BYandCOLLATEnow work on it. These used to fail, for example when a string built with an append operator was used in a query that sorts or removes duplicates.ALTER EXTENSION plx UPDATEsets the collation on the same objects a fresh install does:plx_strbuild, every domain and array type built on it at any depth (such as a domain over a domain, or an array of a domain), and existing columns, composite-type fields, range types, index keys and partition keys of these types. So sorting, the range constructors and operators, hash indexes, and hash, list and range partitions work on them after the update.
Several dialects
- plxjs, plxts, plxpython3, plxruby:
||on a string built with+=or<<, and an interpolation of such a string into SQL, failed at run time withoperator is not unique: plx_strbuild || unknown, because the append makes the variable aplx_strbuild. plx now reads such a variable astextin expressions, as it does for plxphp.=(see plxphp below). - plxjs, plxts, plxphp, plxpython3, plxruby: a loop bound that used
<<,>>,&,|or#, such asi < n >> 1orrange(n >> 1), gave the wrong number of iterations with no error. plx turns an exclusive bound HI intoHI - 1(orHI + 1for arange()with a negative step), and SQL binds-and+tighter than these operators, son >> 1 - 1wasn >> 0. plx now writes(HI) - 1. In plxjs, plxts and plxphp,<binds tighter than&,^and|, soi < n & 6means(i < n) & 6, not a bound: such a loop is now rejected withfor-loop bound must be a single value. Write the bound in brackets,i < (n & 6), or use awhileloop. - plxjs, plxts, plxpython3, plxruby: when plx’s schema name was 50 bytes or
longer (fewer if it needs quotes), the second string append to a local
compiled to numeric addition and failed at run time with
operator does not exist: ... plx_strbuild + unknown. plxruby rejected the body atCREATE FUNCTION. Appends now work with any schema name. Version 2.0.1 was not affected, because it did not qualify the type with the schema. - plxjs, plxts, plxphp, plxruby, plxpython3: a
finally/ensurebody that raised an error on the normal path ran a second time from the error path. It now runs once. - plxruby, plxphp, plxjs, plxts, plxpython3:
.to_s,.to_iand.to_fwere rewritten as casts wherever the text appeared, so a field whose name starts with one of them broke:r.to_statusbecamer::texttatus. They are now rewritten only as whole words, andx.to_s()with an empty argument list works too. - plxruby, plxphp, plxjs, plxts, plxpython3:
ARRAY['x']and a subscript on ajsonb,jsonorhstorevalue (j['a']) are passed to SQL unchanged instead of being rewritten to record field access (ARRAY.x,j.a), which failed at run time. A record key that is not a plain identifier, such asrow['a b'], is now quoted as a column name. In plxphp and plxruby, a record variable namedarrayis no longer read as the SQLARRAYconstructor, which silently returned a one-element array. Such a variable still fails, becausearrayis an SQL reserved word. - plxjs, plxts: a
?and:inside a template literal are now text. They used to be read as a ternary, which turned the literal into broken SQL and made the function fail when it ran. - plxjs, plxts, plxphp: a
switcharm may now end withcontinueor withbreak label. Before, valid code was rejected withswitch fall-through is not supportedorswitch body must consist of case/default labels. - plxjs, plxphp, plxruby, plxpython3, plxts: an expression nested too deeply,
and an integer literal such as
0xFFFFFFFFFFFFFFFFFFthat overflows 64 bits, now report the source line of their statement instead of line 0. plxpython3: aclassorlambdastatement is now reported asclass is not supportedorlambda is not supportedinstead ofdef is not supported.
plxphp
- The
.operator now works on a string built with.=, asdoc/plxphp.mdshows."names: " . $sfailed at run time withoperator is not unique: unknown || plx_strbuild, because.=makes$saplx_strbuild. plx now reads such a variable astextin expressions.
plxts
catch (e: unknown)(a catch variable with a type annotation) now bindse, soe.messageande.sqlstatework. Before, the function failed at run time withmissing FROM-clause entry for table e.
plxpython3
range(a, b, step)with a negative literal step, such asrange(10, 0, -3), now counts down as in Python. It failed at run time withBY value of FOR loop must be greater than zero. A negative step held in a variable still fails that way.call(f"...")emitted an unterminated quoted identifier. An f-string procedure name is now rejected.
plxruby
- A NULL in a
raiseoption no longer replaces your error. Before, if a value indetail:,hint:,column:orconstraint:was NULL, either interpolated (detail: "looked up #{u}") or bare (detail: u), theraisefailed withRAISE statement option cannot be null(SQLSTATE 22004). Your message, SQLSTATE, detail and hint were lost, and a handler for your SQLSTATE did not run. These options now get the same fallback as the message: each NULL value becomes an empty string. The generated SQL changes for everyraisethat has options. Behaviour changes only for calls that used to fail with 22004. A NULLerrcode:still fails with 22004. raise message: "...", ...now raises that message. Before, it compiled to a syntax error. Araisewith both a message andmessage:is rejected atCREATE FUNCTIONinstead of failing when it runs.
plxgo
+between strings now joins them (#18, #23). plx writes it as||when every operand is a string literal, astringlocal, atextorvarcharparameter, or a call that returns a string. If any operand has a type plx cannot see, such as a row field, an array,jsonbor a SQL function call,+stays SQL+and fails when the function runs. Before,strconv.Itoa(n) + "x"failed when the function ran, withoperator does not exist: text + unknown. A NULL operand makes the result NULL.msg := "a" + snow declaresmsgastext.s += xon a string appends. It used to becomes := s + (x), which failed when it ran. plx now writess := s || (x)when it can see thatxis a string, by the same rules as+. A NULLxmakessNULL.s += 1on a string is rejected atCREATE FUNCTION, as in Go. When plx cannot see the type ofx, such as a row field or a SQL function call, it still writess := s + (x), which fails when the function runs.make(...)is not supported, but the error named something else: a syntax error at[, a type that could not be inferred, or a case clash between the type name and a variable. plx now rejectsmakeatCREATE FUNCTIONwithmake is not supportedand suggests a slice literal orarray_fill.
plxcobol
EXIT PERFORM CYCLEinside aPERFORM VARYINGwith a step other than BY 1, or with a compoundUNTIL, now runs the step. Before, the loop never ended.- An
OCCURSitem with aVALUEclause now starts with every element set to that value. Before, the function was created but failed at every call withmalformed array literal. - An
EVALUATEwhose onlyWHENphrases are stacked directly beforeWHEN OTHER, or that has onlyWHEN OTHERor noWHENat all, no longer fails at every call. DISPLAYacceptsNO ADVANCINGwithoutWITH. Before,NOandADVANCINGwere read as data names and the statement failed.
plxtsql
- The dialect chapter and doc/LIMITATIONS.md now say that a
CATCHblock undoes the database changes itsTRYblock made before the error. SQL Server keeps them. plx cannot, becauseTRYbecomes a plpgsql exception block, which rolls back to a savepoint. The behaviour is the same as in 2.0.1; only the documentation is new. It shows how to keep such a write. - A
CASEexpression works inSET,RETURN,DECLAREinitializers,IFandWHILEconditions, andSELECTassignment lists. Before, plx cut the expression at the CASE’sELSEorEND, and the function was rejected withunexpected ENDorunexpected keyword in statement position. AnIFcondition that holdsIIF(...)now compiles too. CASTmaps T-SQL type names the wayCONVERTandDECLAREdo. Before,CAST(x AS nvarchar(5))failed when the function ran, withtype "nvarchar" does not exist.NVARCHAR,NTEXT,VARCHAR(MAX),NVARCHAR(MAX),DATETIME,DATETIME2,UNIQUEIDENTIFIER,IMAGEandVARBINARY(MAX)now work inCAST.CASTtoNVARCHARwithout a length givesvarchar(30), as in SQL Server.TINYINT,SMALLMONEY,SMALLDATETIME,SYSNAME,BINARYandVARBINARY(n)still fail, because the PostgreSQL type would not keep SQL Server’s value.CONVERT(varchar(MAX), x)andCONVERT(nvarchar(MAX), x)givetext. Before, the function was rejected withsyntax error at or near "MAX".(MAX)on any other type, such asCONVERT(numeric(MAX), x)orCONVERT(char(MAX), x), still fails, as it does in SQL Server.+joins strings, andSET @s += eappends to a string variable. Before, both failed when the function ran, for example'1' + '2'withoperator is not unique: unknown + unknownandSET @s += 'b'withoperator does not exist: character varying + unknown. plx writes+as||only when every operand is a known string, such as aVARCHARvariable, a string literal orISNULL(@s, '0'), so'1' + '2'gives12andISNULL(@s, '0') + '5'gives05.SET @s += ebecomes||only when@sis a string variable andeis a known string. Any other+stays+:'1' + 2still adds and gives 3, and a string variable next to a number still fails when the function runs. A NULL operand makes the result NULL. See doc/plxtsql.md for the full list of known strings.DECLARE,CASTandCONVERTaccept SQL Server’s type names of several words, such asCHARACTER VARYING(n)andNATIONAL CHARACTER VARYING(n). Before,DECLARErejected them. A value of one of the variable-length string types among them is now a known string, so+joins it with||.CHARACTER(n)andNATIONAL CHARACTER(n)are fixed length, likeCHAR(n), so+stays+for them.
Test suite
- The differential test records the exact result each documented divergence documents, and only that result is exempt. Before, a dialect could return any wrong value for an exempted call and the check still passed. This affects the exemptions for plxruby, plxphp, plxjs, plxts, plxpython3 and plxcobol.
- The differential test records each result as JSON. Before, a dialect that
returned the literal text
<NULL>,<EMPTY>orERROR <code>was counted as matching a reference that returned NULL, returned an empty string or raised. No current case was affected. - The fuzzer reports a statement cancelled at its timeout as a hang and an out-of-memory error as a finding. Before, both counted as clean. It also stops with an error when the plx languages are missing from the target database; before, it silently tested nothing.
make metacheckchecksMETA.jsonthe way PGXN does at upload time: license identifiers, unknown keys, empty text fields, the meta-spec version, the prereqs structure and version ranges,no_index, the bugtracker and repository value types, and full-string version patterns. Before, it passed files that PGXN rejects. It reports a value of the wrong type as an error instead of crashing.test/run_corpus.pycompares result rows and notices in order, and compares psql output line by line. Before, it compared unordered token sets, so swapped values, mispaired rows and reversed results passed. A value of-or+, spaces inside a value or message, and the position of an empty row now count too. This affects the plxruby and plxcobol corpus cases.- A
test/run_corpus.pycase that records an expected error now passes only when psql prints each recordedERROR:line word for word. Before, it passed when about half the words of the expected error showed up anywhere in the output, and theCONTEXT:line alone was enough, so a different error raised at the same statement passed. The runner’s self-test now covers this check. Two reject cases recorded messages plx never printed; they now record the real ones. - CI now runs the Ruby corpus on every PostgreSQL version it tests, with the
new
make corpuscheck. Before, nothing ran the corpus automatically, so a change that broke a corpus case could merge unnoticed.test/run_corpus.pynow stops with a clear message when it cannot find psql, instead of a Python traceback.
Changed
make differentialcheckalso runs the self-tests of the fuzzer and oftest/run_corpus.py. Neither self-test needs a server.test/run_corpus.pyuses thepsqlonPATH(orPLX_PSQL) and the libpq environment variables instead of a fixed/usr/local/pgsqlpath. Its self-test runs before it touches the server.
Added
ident(),literal()andraw()may wrap an interpolation in a SQL argument (plxphp:{ident($t)}):ident(e)splicesquote_ident(e),literal(e)splicesquote_nullable(e), andraw(e)spliceseunquoted, for a trusted SQL fragment. Each makes the call dynamic.ROUNDEDin plxcobol. WriteROUNDEDafter a receiving field to round half away from zero instead of truncating. See the PICTURE entry under Breaking changes.WITH NOWAIT,WITH SETERRORandWITH LOGon plxtsqlRAISERROR.WITH LOGalso writes the message to the server log. See theRAISERRORentry under Breaking changes.
[2.0.1] - 2026-08-25
Packaging only. The extension is unchanged at 2.0.0, so there is no
ALTER EXTENSION plx UPDATE for this release and nothing to install if you are
already on 2.0.0. Only the distribution metadata changed, which is why the
distribution version moved and the extension version did not.
Fixed
META.jsonlisted the tagpl/sql, which PGXN rejects: a Tag may not contain a slash. It is nowplsql. This was found only when PGXN refused the 2.0.0 upload, since the check happens at upload time, after a release has been tagged and published.
Added
make metacheck(test/check_meta.py) validatesMETA.jsonagainst the PGXN Meta Specification v1 before a release rather than at upload time, and runs in CI. It reproduces the rejection above.Worth recording, because the prose and the schema disagree and the prose is the trap: the spec text says a Tag may contain no “slash, backslash, control, or space” characters, but that sentence describes a Term. The Tag schema is
^[^/\\\p{Cntrl}]{2,}$, which permits spaces. Sosql serveris a valid tag and an invalid term, and only the slash inpl/sqlwas ever a problem.
[2.0.0] - 2026-08-24
Major release for one behaviour change: interpolating a NULL now propagates it
instead of rendering an empty string. Upgrade with
ALTER EXTENSION plx UPDATE TO '2.0.0' after installing the new module.
Changed
- Interpolating a NULL propagates it. An interpolated value is concatenated
as-is, so the whole string becomes NULL the way SQL
||does. Previously every interpolated value was wrapped inCOALESCE((x)::text, ''), so a missing value silently became a plausible-looking string:
-- return "#{country}-#{region}-#{code}" with a NULL region
-- before: 'US--123' a string that inserts, indexes and joins like data
-- now: NULL
This affects plxruby, plxphp, plxjs, plxts, plxpython3 and
plxgo. plxplsql and plxtsql already propagated and are unchanged.
plxcobol builds strings through the plx_strbuild accumulator, whose
append treats a NULL as nothing to append by design, and is unchanged.
A message built for RAISE is the exception and keeps each interpolated
value’s empty-string fallback, so one NULL cannot swallow a diagnostic and
the literal text of the message survives.
plxgo:fmt.Sprintfis lowered to a SQL concatenation rather thanformat(), which is what allows the propagation, sinceformat()renders a NULL operand as empty and cannot be made to do otherwise. Observable output is otherwise unchanged: verbs render their operand as text, a-flag and a width still pad (now throughrpad/lpad), other flags and the precision field are still dropped, and%%is still a literal percent.
Upgrading
ALTER EXTENSION plx UPDATE TO '2.0.0' does not change any function that
already exists. plx transpiles at CREATE FUNCTION time and stores the result
in pg_proc.prosrc, so a function in the catalog keeps the plpgsql it was
created with. The new behaviour arrives the next time that function’s DDL is
run, which for most installations means the next deployment.
To find the functions a redeployment will change:
SELECT p.oid::regprocedure
FROM pg_proc p JOIN pg_language l ON l.oid = p.prolang
WHERE l.lanname LIKE 'plx%'
AND EXISTS (SELECT 1
FROM regexp_split_to_table(p.prosrc, E'\n') AS ln
WHERE ln LIKE '%COALESCE((%)::text, ''''%'
AND ln NOT LIKE '%RAISE %')
ORDER BY 1;
Each result interpolates a value that will become NULL when the value is NULL.
Where the old rendering was wanted, make it explicit with coalesce(x, '') in
the body.
The same applies to a dump taken from an earlier release and restored into 2.0.0: the restored functions keep the plpgsql stored in the dump, including the empty-string rendering, until their DDL is run again.
[1.3.2] - 2026-08-24
Code-only patch release (no catalog changes) carrying a plxgo fix for
fmt.Sprintf and the differential check that found it. Upgrade with
ALTER EXTENSION plx UPDATE TO '1.3.2' after installing the new module.
Fixed
plxgo:fmt.Sprintfin expression position passed its Go format string straight into SQLformat(), so any verb other than%sraisedunrecognized format() type specifierwhen the function was called. The transpile succeeded and the failure only appeared at run time, which madefmt.Sprintf("%d", n), the ordinary way to format an integer in Go, produce a function that could not run. Go’s verbs are now rewritten to the%sthatformat()understands, keeping a-flag and a width and dropping the flags and precisionformat()has no equivalent for. A%that starts no directive is escaped rather than passed through, since a lone%is itself an error toformat(). Found by the new differential check.Note that the verbs which change an operand’s representation rather than its padding render what
%srenders, sofmt.Sprintf("%x", 255)yields255and notff. Convert explicitly where the representation matters. This is recorded indoc/plxgo.mdanddoc/LIMITATIONS.md.
Added
make differentialcheck(test/differential.py): each case is one program written as a plpgsql reference and once per dialect, called with the same arguments and required to agree with the reference on both values and error SQLSTATEs. Intended divergences are recorded per case with a reason and reported separately, and the check fails if a recorded divergence stops happening, so a documented limitation cannot outlive its documentation.doc/MIGRATION.md, a migration-led page comparing plx against rewriting by hand, an embedded PL,ora2pg, and leaving the logic in the application, including how to leave plx while keeping the generated plpgsql.
Changed
- Internal refactor of the transpiler behind a
PlxSurface.parse_bodyvtable (#2). The single ~11k-linesrc/plx_transpile.cis split into a dialect-neutral engine (declared in the newsrc/plx_engine.h) plus per-dialect front ends insrc/plx_dialect_*.cand the sharedsrc/plx_parse_brace.c; the hardcodedblock_styledispatch is replaced by a per-dialectparse_bodyfunction pointer. No functional change; generated plpgsql is byte-identical and all regression tests pass. Adding a dialect no longer touches shared code. - Follow-up to the above: relocated the dialect-neutral
plx_diag_prefix()helper out ofsrc/plx_dialect_ruby.cand back into the engine (src/plx_transpile.c), where its other callers (Python and the brace front end) already live. No functional change. - Tightened linkage and removed dead code left by the refactor: the six
single-dialect
plx_*_parse_bodyfront ends are nowstatic(only the sharedplx_brace_parse_bodykeeps a prototype inplx_engine.h); dropped the write-onlyCtx.ntfield; and corrected a stale COBOL comment about a keyword table the surface does not carry. No functional change.
[1.3.1] - 2026-07-16
Code-only patch release (no catalog changes) carrying the memory-safety and
robustness fixes from the full-repo transpiler audit (#1). Upgrade with
ALTER EXTENSION plx UPDATE TO '1.3.1' after installing the new module.
Fixed
- Missing capacity guard on the trailing
T_EOFtoken write inlex(): a source that lexed to exactlycap-1tokens overflowed the token array by one (heap overflow / SIGSEGV). All token writes are now guarded. plx_strbuildsb_ensure()doubled its capacity inint32, which could wrap negative near ~1GB and spin forever while passing a bogus size torepalloc. Growth is now computed in 64-bit and clamped toMaxAllocSize, so an oversized request raises the standard allocation error cleanly.- The recursion-guarded transpiler entry points now call
check_stack_depth(), so deeply nested hostile input raises a clean error honoringmax_stack_depthinstead of overflowing the C stack and crashing the backend. - The Python lexer raises a clean “indentation nested too deeply” error at the
indent-stack limit instead of emitting an unbalanced
T_INDENTthat mis-nested every enclosing block. - Raw single-quoted strings in Ruby and PHP keep backslashes literal (
'\n','C:\temp'); only\\and\'are special. Double-quoted and Python/JS single-quoted escapes are unchanged. - PHP
${name}curly interpolation in double-quoted strings is now recognized (was emitted verbatim). - Non-decimal integer literals (
0x/0o/0b, with_group separators) are lexed as a single token and rewritten to decimal, keeping generated plpgsql portable to PG13-15 (which accept0x/0o/0bonly on PG16+). A literal that overflows 64 bits raises a clean “integer literal out of range” error rather than emitting invalid SQL.
Other
- Declare the built-in dialect descriptors in
plx.h, clearing-Wmissing-variable-declarationsacross the dialect translation units.
[1.3.0] - 2026-07-15
Trigger row mutation across dialects, plus the cookbook and limitations
documentation set. Upgrade with ALTER EXTENSION plx UPDATE TO '1.3.0' after
installing the new module (no catalog changes).
Added
- plxtsql: a trigger can now assign to
NEWfields withSET NEW.col = e, which lowers toNEW.col := e, so a Transact-SQL trigger can rewrite the row and not only validate it. ASETwhose target is a qualified name with a top-level=is an assignment;SET NOCOUNT ONand other session options are still ignored. Qualified names also emit without a stray space around the dot. Covered by a new plxtsql trigger regression test. - A verified cookbook for each of the nine dialects (
doc/cookbook/) and a consolidated gaps-and-limitations page (doc/LIMITATIONS.md), both on the documentation site.
With the plxphp arrow assignment (1.2.2) and this plxtsql SET NEW.col, assigning
to a trigger’s NEW fields is supported across the dialects in the dialect’s own
idiom. See each dialect’s cookbook trigger recipe.
[1.2.2] - 2026-07-15
Code-only patch release (no catalog changes). Upgrade with
ALTER EXTENSION plx UPDATE TO '1.2.2' after installing the new module.
Fixed
- plxphp: assigning to a record field with the arrow form (
$NEW->col = e, the documented and idiomatic PHP spelling) raised “unsupported operator in statement”; only the array-element form worked. The arrow lvalue now lowers toNEW.col := e, so trigger functions can stampNEWfields with$NEW->col. Covered by a new plxphp trigger regression test.
[1.2.1] - 2026-07-15
Code-only patch release (no catalog changes). Upgrade with
ALTER EXTENSION plx UPDATE TO '1.2.1' after installing the new module.
Fixed
- Build on PostgreSQL 19 and 20 with a C23 toolchain (for example gcc 15). There,
pg_noreturnexpands to the standard[[noreturn]]attribute, whose placement is strict; plx wrote it after the storage class (static pg_noreturn void), which C23 rejects. It is now the first token of the declaration (pg_noreturn static void), matching PostgreSQL’s own convention, and still compiles on 13 through 18. The full suite passes on PostgreSQL 13 through 18 plus 19beta and 20devel built from source. See doc/COMPATIBILITY.md. - A plxgo regression test (0-based indexing) declared its accumulator with
:=from a non-inferable expression, so the function failed to create and the test silently checked the failure rather than indexing; it now usesvar sum int.
[1.2] - 2026-07-15
Added
plxplsql, an Oracle PL/SQL dialect. PL/SQL and plpgsql are both Ada-descended, so most of the language (DECLARE/BEGIN/EXCEPTION/END,IF/ELSIF,LOOP/WHILE/FOR,CASE,:=,||, cursors,%TYPE) passes through unchanged. plxplsql is a layout-preserving rewriter that translates the Oracle spellings:NUMBER/VARCHAR2/PLS_INTEGER/… types,DBMS_OUTPUT.PUT_LINE,RAISE_APPLICATION_ERROR,EXECUTE IMMEDIATE,FROM DUAL,NVL,seq.NEXTVAL,SYSDATE, andCURSOR c IS. Function signatures use PostgreSQL types; the body is PL/SQL. See doc/plxplsql.md.plxts, a TypeScript dialect: the plxjs dialect pluslet x: Ttype annotations, which map TypeScript types (number,string,boolean,bigint,T[],T | null) to SQL types and otherwise accept a SQL type name verbatim. See doc/plxts.md.plxtsql, a Transact-SQL (SQL Server) dialect. T-SQL is not Ada-descended, so plxtsql is a restructuring front end with its own tokenizer and parser: it hoists@-variables and inlineDECLAREinto the plpgsqlDECLAREblock, rewritesSET/SELECT @x =assignments, turnsIF/WHILE ... BEGIN ... ENDintoTHEN ... END IF/LOOP ... END LOOP, mapsTRY/CATCHto anEXCEPTIONblock, and translates the type and function libraries (INT,NVARCHAR(MAX),DATETIME,ISNULL,IIF,CONVERT,LEN,GETDATE,PRINT,RAISERROR,THROW, …). See doc/plxtsql.md.plxgo, a Go dialect. Go’s parenlessif/for,:=short declarations with type inference,for ... range, and no-fallthroughswitchdiffer enough from plpgsql that plxgo is a restructuring front end with its own tokenizer (including Go’s automatic semicolon insertion) and parser. It hoistsvar/:=/constdeclarations, rewrites assignment (includinga, b = x, yparallel assignment toSELECT ... INTO), turnsif/for/switchintoIF/WHILE/FOR/FOREACH/IF-ELSIF, mapspanic/fmt.PrintlntoRAISE, translates the type and a stdlib library (strings,math,strconv,len,append, type conversions), and providesemit/execute/range query()SQL intrinsics. See doc/plxgo.md.plxcoboltables:OCCURS nmaps aWORKING-STORAGEitem to a PostgreSQL array, withWS-ARR(i)subscripts as both lvalues and expressions andPERFORM v OVER ARRAYiteration.doc/DEBUGGING.md: correlating runtime errors to your dialect source, and aplx_source()helper that recovers the embedded original body.
Hardened
A whole-project adversarial audit (fresh-eyes review of every front end plus mutation fuzzing of all nine dialects) fixed a set of transpiler defects, all now covered by tests:
- Backend crashes: an unbounded intrinsic argument list read past a fixed stack
array (17+ arguments to
call/query/execute); the Python parser had no recursion-depth guard (deep nesting exhausted the C stack); a COBOLUSAGEclause at end of input stepped past the token-array sentinel. All now error. - Backend hangs: a stray
)/](Go) or danglingELSE(T-SQL) at statement position, and afor/if/else-ifrecursion in Go, could spin or overflow; all now error cleanly. - Wrong or invalid output: Go slice subscripts are 0-based (rewritten to
PostgreSQL’s 1-based arrays); a COBOL compound
UNTILno longer misfolds into the integer-FORbound; a JS/PHPswitchwith no case, onlydefault, or acaseafterdefaultnow errors instead of emitting uncompilable plpgsql; the?:elvis operator, a value-less Goconst, and several empty-argument forms (fmt.Println(),panic(),PRINT;,RAISERROR()) are handled or rejected.
The mutation fuzzer (test/fuzz.py) and corpus now cover all nine dialects.
Upgrading
ALTER EXTENSION plx UPDATE TO '1.2'(seeplx--1.1.1--1.2.sql).
1.1.1 - 2026-07-15
Code-only patch release (no catalog changes). Upgrade with
ALTER EXTENSION plx UPDATE TO '1.1.1' after installing the new module.
Fixed
- Compilation on PostgreSQL 13, 14, and 15:
plx_strbuild.cincludedvaratt.hunconditionally, but that header was only split out ofpostgres.hin PostgreSQL 16, so plx 1.1 did not build on 13-15. Guard the include. The full regression suite now passes on PostgreSQL 13 through 18 (verified in CI). - plxcobol: a crash (out-of-bounds read) on a body truncated at
PERFORM VARYING ... UNTIL; it now errors cleanly. - plxcobol:
ADD a b GIVING cand other multi-addendADD/SUBTRACTforms were rejected; parse an operand list.MULTIPLY/DIVIDEremain single-source. - plxcobol: multi-argument SQL function calls in expressions (
mod(a, b)) were broken because the tokenizer stripped commas everywhere; keep commas inside parentheses. - plxcobol: a
GREATER/LESS ... OR EQUALcomparison at the end of a condition dropped the “OR EQUAL”; aPICTURErepeat-count integer overflow; and an unterminated string literal silently lost its last character.
Added
- Continuous integration (GitHub Actions) running the full 9-suite regression on a PostgreSQL 13 through 18 matrix.
- plxcobol coverage in the fuzzer and the corpus runner, and plxcobol rejection tests in the error suite.
1.1 - 2026-07-14
Added
plxcobol, a COBOL dialect (ISO/IEC 1989:2023, COBOL 2023, free format), at full plpgsql construct parity. It has its own front end (verb-driven tokenizer and parser):WORKING-STORAGEdeclarations withPICTURE/TYPE/CONSTANTmapped to SQL types;MOVE/COMPUTEand theADD/SUBTRACT/MULTIPLY/DIVIDEverbs;IF/END-IF;EVALUATE(simple andEVALUATE TRUE);PERFORMin theUNTIL,VARYING,TIMES, inline, query (OVER), and array (OVER ARRAY) forms;GOBACK RETURNING,RETURN-NEXT,RETURN-QUERY;EXECUTE; cursors (OPEN-CURSOR/FETCH-CURSOR/MOVE-CURSOR/CLOSE-CURSOR); exception handling (BEGIN-TRY/WHEN/END-TRY) with stacked diagnostics viaGET;RAISE,DISPLAY,ASSERT,CALL,COMMIT/ROLLBACK. Data names are mapped to plpgsql identifiers (lower-cased, hyphens to underscores). See doc/plxcobol.md.- plxcobol:
STRING-APPEND <expr> TO <var>, which lowers to theplx_strbuildstring builder (the COBOL counterpart of the other dialects' append operators), and%as the modulo operator in expressions. - Regression suite
plxcoboladded tomake installcheck. - Benchmarks now cover plxcobol (
bench/BENCHMARKS.md): it matches plpgsql on arith, strbuild, iter, and call, and is about 1.3x on the branch workload becauseEVALUATElowers toCASE. APERFORM VARYING v FROM a BY 1 UNTIL v > bcounting loop lowers to a plpgsql integerFORloop (otherPERFORM VARYINGforms useWHILE).
Upgrading
- Existing 1.0 installations upgrade in place with
ALTER EXTENSION plx UPDATE TO '1.1'(seeplx--1.0--1.1.sql).
1.0 - 2026-07-14
Initial release.
Added
- Dialect-pluggable front end that transpiles to plpgsql at
CREATE FUNCTIONtime. The generated plpgsql is stored inpg_proc.prosrcand executed by the standard plpgsql handler, with no separate language runtime in the backend. - Dialects, each at full plpgsql construct parity:
plxruby, a Ruby dialect.plxphp, a PHP dialect.plxjs, a JavaScript dialect.plxpython3, a Python dialect.
- Language names carry a
plxprefix, so the extension coexists with the native PL/Ruby and PL/PHP in the same database. - Coverage of the plpgsql surface from every dialect: typed local declarations
and inference, control flow, loops with labels, query iteration, array
iteration, single-row fetch, dynamic SQL, cursors, diagnostics, set-returning
functions, error raising and handling, assertions, and trigger functions with
NEW,OLD, and theTG_variables. - Idempotent re-transpile: a sentinel comment records the transpiler version and embeds the original source, so re-processing a stored function is a no-op.
plx_strbuild, an expanded-object string builder with amortized-O(1) append, addressing plpgsql’s O(n2) in-loop string concatenation. The transpiler lowers the dialect append operators (s << x,$s .= x,s += xon a string) onto it. On PostgreSQL 18 this is about 170x faster than thes := s || 'x'idiom and faster than the native PLs' own append. The acceleration relies on the PostgreSQL 18 planner support requestSupportRequestModifyInPlace; on PostgreSQL 13 to 17 the builder is correct but not accelerated.- Benchmark harness (
bench/run_bench.py) covering five workloads against plpgsql, the plx dialects, PL/Perl, PL/Python3, and the native PL/Ruby and PL/PHP, with results inbench/BENCHMARKS.md. - Regression suite (
make installcheck) across the dialects, features, output, errors, and the string builder, plus a corpus runner and a fuzzer. - Documentation: per-dialect chapters, a plpgsql parity matrix, a user guide drawn from the PL/pgSQL manual, an architecture document, a transpiler specification, and a compatibility note.
Compatibility
- Tested against PostgreSQL 13, 14, 15, 16, 17, and 18. The full regression suite passes on each.