Contents
plxtsql: the Transact-SQL (SQL Server) dialect
plxtsql lets you write PostgreSQL functions with Transact-SQL syntax, the
procedural language of Microsoft SQL Server and Sybase. At CREATE FUNCTION
time plx transpiles the body to plpgsql and stores the plpgsql in
pg_proc.prosrc. The function then runs on the standard plpgsql interpreter.
Unlike plxplsql, T-SQL is not Ada-descended, so plxtsql is a restructuring front
end rather than a pass-through rewriter. It has its own tokenizer and parser and
emits plpgsql directly. It handles the parts of T-SQL that differ structurally
from plpgsql: @-prefixed variables, DECLARE anywhere in the body, IF/WHILE
bodies written as BEGIN ... END instead of THEN ... END IF, and
TRY/CATCH. This makes plxtsql a practical path for moving SQL Server stored
logic into PostgreSQL.
Setup
CREATE EXTENSION plx;
Function basics
The body is a sequence of T-SQL statements. Local variables are declared with
DECLARE @name type and referenced with the @ sigil; plx hoists every
declaration into a plpgsql DECLARE block and drops the sigil in the generated
code.
CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxtsql AS $$
DECLARE @g varchar(10);
IF @score >= 90
SET @g = 'A';
ELSE IF @score >= 80
SET @g = 'B';
ELSE
SET @g = 'F';
RETURN @g;
$$;
An outer BEGIN ... END wrapping the whole body is optional; if present it is
unwrapped (the declarations inside still hoist).
Function signatures use PostgreSQL types
The body is T-SQL, but the function signature (the parameter and return
types in CREATE FUNCTION) is parsed by PostgreSQL before plx sees the body, so
it must use PostgreSQL type names: write RETURNS timestamp, not
RETURNS DATETIME. Parameters are referenced in the body with the @
sigil (@score above binds to the parameter score). Inside the body, T-SQL
type names in DECLARE are translated (see below).
Transact-SQL to PostgreSQL translations
Identifiers
| T-SQL | PostgreSQL |
|---|---|
[Order Details], [user] |
"order details", "user" |
"Name" |
"Name" |
[a]]b], "a""b" |
"a]b", "a""b" |
A delimited identifier keeps its delimiters, so it can hold a space or a reserved word.
- A
[bracketed]name is folded to lower case, then quoted. So[Orders]matches a table created asordersorOrderswithout quotes, as in SQL Server under its default case-insensitive collation. - A
"quoted"name keeps its case, as in PostgreSQL. Use it to reach an object created with a mixed-case quoted name:"Name"matches a column created as"Name", not one created asname.
Variables and assignment
| T-SQL | plpgsql |
|---|---|
DECLARE @x int |
hoisted to DECLARE x integer; |
DECLARE @x int = 5 |
x integer; plus x := 5; at that point |
DECLARE @a int, @b int |
both hoisted |
SET @x = e |
x := e; |
SET @x += e |
x := x + (e); (also -=, *=, /=, %=) |
SET @s += e, @s a string variable and e a known string |
s := s || (e); |
SELECT @x = e (no FROM) |
x := e; |
SELECT @x = a, @y = b |
x := a; y := b; |
SELECT @x = col FROM t ... |
FOR r IN SELECT col AS c1 FROM t ... LOOP x := r.c1; END LOOP; |
SELECT @x = col FROM t ... assigns once per row, as in SQL Server. After the
statement, @x holds the value from the last row. When no row matches, @x
keeps the value it had before. An expression in the list that reads one of the
variables the same SELECT assigns, such as SELECT @t = @t + v FROM t, is
rejected when you create the function. Use an aggregate instead, for example
SELECT @t = SUM(v) FROM t or SELECT @s = string_agg(name, ',') FROM t.
An assignment to an integer variable truncates a fraction toward zero, as in
SQL Server: DECLARE @i int = 2.7 gives 2, SET @i = -2.7 gives -2, and
SET @i += 1.5 adds 1.5 and then truncates. This applies to DECLARE, SET
and SELECT @x = .... See
Conversion to an integer type.
Control flow
| T-SQL | plpgsql |
|---|---|
IF cond stmt |
IF cond THEN stmt END IF; |
IF cond BEGIN ... END |
IF cond THEN ... END IF; |
IF cond ... ELSE ... |
IF cond THEN ... ELSE ... END IF; |
ELSE IF ... |
nested ELSE IF ... END IF; |
WHILE cond BEGIN ... END |
WHILE cond LOOP ... END LOOP; |
BREAK |
EXIT; |
CONTINUE |
CONTINUE; |
RETURN e |
RETURN e; |
The condition after IF/WHILE runs up to the start of the body (a statement
keyword or BEGIN); there is no THEN keyword in T-SQL.
Messages and errors
| T-SQL | plpgsql |
|---|---|
PRINT e |
RAISE NOTICE '%', e; |
RAISERROR('msg', sev, state) with sev 10 or lower |
RAISE NOTICE '%', 'msg'; |
RAISERROR('msg', sev, state) with sev 11 or higher, -1, or ERROR_SEVERITY() |
RAISE EXCEPTION '%', 'msg'; |
RAISERROR('msg', @sev, state) |
IF sev <= 10 AND sev <> -1 THEN RAISE NOTICE ... ELSE RAISE EXCEPTION ... END IF; |
RAISERROR(...) WITH LOG |
adds RAISE LOG '%', 'msg'; before the message |
THROW n, 'msg', s |
RAISE EXCEPTION '%', 'msg'; |
THROW; (bare, in CATCH) |
RAISE; (re-raise) |
BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH |
BEGIN ... EXCEPTION WHEN OTHERS THEN ... END; (undoes the writes made in TRY; see below) |
ERROR_MESSAGE() |
SQLERRM |
RAISERROR follows SQL Server’s severity rule. A severity of 10 or lower is an informational message: the function prints it as a notice and continues, and a CATCH block does not catch it. A severity of 11 or higher raises an error. Two values always raise an error: -1, which in SQL Server means the severity stored for the message (plx cannot look that up), and ERROR_SEVERITY(), because only an error reaches a CATCH block. Any other negative severity counts as 0. When the severity is a variable, plx checks it at run time, and a NULL severity raises an error. After the closing parenthesis you can write WITH followed by NOWAIT, SETERROR or LOG, separated by commas. NOWAIT needs no translation because PostgreSQL sends every notice to the client at once. SETERROR has no effect because plxtsql does not support @@ERROR. LOG also writes the message to the server log. Any other option is an error when you create the function.
RAISERROR’s format arguments (its %d/%s printf-style substitutions) are not
applied; the message argument is emitted as-is.
A CATCH block undoes the writes made in TRY
Caution: when an error reaches CATCH, plx undoes every database change
that the TRY block made before the error. SQL Server keeps those changes when
XACT_ABORT is OFF, its default. plx cannot keep them: TRY becomes a
plpgsql block with an EXCEPTION clause, and PostgreSQL rolls such a block back
to a savepoint when it catches an error. Variables keep the values they had
when the error happened, as in SQL Server.
CREATE TABLE logt(msg text);
CREATE FUNCTION r1() RETURNS text LANGUAGE plxtsql AS $$
BEGIN TRY
INSERT INTO logt VALUES ('attempt');
RAISERROR('fail', 16, 1);
END TRY
BEGIN CATCH
INSERT INTO logt VALUES ('caught');
END CATCH
RETURN 'done';
$$;
SELECT r1();
SELECT string_agg(msg, ',') FROM logt;
SQL Server leaves attempt,caught in logt. plx leaves only caught.
To keep a write when the TRY block fails, make it outside the TRY block:
- Move a write that must survive, such as an audit or error-log row, before
BEGIN TRY. - Or make it in the
CATCHblock, which runs after the rollback. To use a value computed inTRY, assign it to a variable there; the variable keeps its value.
Types (in DECLARE)
| T-SQL | PostgreSQL |
|---|---|
INT, INTEGER |
integer |
BIGINT, SMALLINT, TINYINT |
bigint, smallint, smallint |
BIT |
boolean |
DECIMAL(p,s), NUMERIC(p,s), DEC(p,s) |
numeric(p,s) |
DECIMAL, NUMERIC, DEC (no precision) |
numeric(18,0), as in SQL Server |
MONEY |
numeric(19,4) |
FLOAT, REAL |
double precision, real |
VARCHAR(n), NVARCHAR(n) |
varchar(n) |
VARCHAR(MAX), NVARCHAR(MAX) |
text |
CHAR(n), NCHAR(n) |
char(n) |
TEXT, NTEXT |
text |
DATE, TIME |
date, time |
DATETIME, DATETIME2, SMALLDATETIME |
timestamp |
DATETIMEOFFSET |
timestamptz |
UNIQUEIDENTIFIER |
uuid |
VARBINARY(n), VARBINARY(MAX) |
bytea |
SQL Server’s names of several words mean the same as the one-word names, here
and in CAST and CONVERT: CHARACTER VARYING and CHAR VARYING mean
VARCHAR, NATIONAL CHARACTER VARYING and NATIONAL CHAR VARYING mean
NVARCHAR, CHARACTER means CHAR, NATIONAL CHARACTER and NATIONAL CHAR
mean NCHAR, BINARY VARYING means VARBINARY, and DOUBLE PRECISION means
FLOAT.
An unrecognized type name is passed through, so PostgreSQL type names also work.
Functions
| T-SQL | PostgreSQL |
|---|---|
ISNULL(a, b) |
coalesce(a, b) |
IIF(c, a, b) |
CASE WHEN c THEN a ELSE b END |
CONVERT(type, e) |
CAST(e AS type) (the style argument is ignored) |
LEN(x) |
length(rtrim(x)) (trailing spaces are not counted, as in SQL Server) |
DATALENGTH(x) |
octet_length(x) |
CHARINDEX(sub, str) |
strpos(str, sub) (arguments swapped) |
GETDATE(), SYSDATETIME() |
now() |
NEWID() |
gen_random_uuid() |
CEILING(x) |
ceil(x) |
@@IDENTITY |
lastval() |
Most standard functions (COALESCE, SUBSTRING, UPPER, LOWER, ABS,
ROUND, …) are already valid in PostgreSQL and pass through.
Types in CAST
CAST(e AS type) keeps its form. plx maps the T-SQL type names below with the
table that DECLARE and CONVERT use. Any other type name passes through, so
PostgreSQL type names work.
| T-SQL | PostgreSQL |
|---|---|
NVARCHAR(n) |
varchar(n) |
NVARCHAR (no length) |
varchar(30) |
VARCHAR(MAX), NVARCHAR(MAX), NTEXT |
text |
BIT |
boolean |
MONEY |
numeric(19,4) |
DECIMAL, NUMERIC, DEC (no precision) |
numeric(18,0) |
INT, INTEGER, BIGINT, SMALLINT |
the same type, with the value truncated first (see below) |
DATETIME, DATETIME2 |
timestamp |
DATETIME2(n) |
timestamp(n); DATETIME2(7) gives timestamp (6 digits) |
UNIQUEIDENTIFIER |
uuid |
VARBINARY(MAX), IMAGE |
bytea |
plx does not map TINYINT, SMALLMONEY, SMALLDATETIME, SYSNAME,
BINARY or VARBINARY(n) in CAST, because the PostgreSQL type would not
keep SQL Server’s value. CAST(300 AS tinyint) is an error in SQL Server but
300 in smallint, and CAST('abc' AS varbinary(1)) keeps 1 byte in SQL Server
but 3 in bytea. The call fails with type "tinyint" does not exist. Cast to
the PostgreSQL type you want instead, such as smallint or bytea.
A mapped type takes the PostgreSQL rules for its values, as in DECLARE and
CONVERT. These values differ from SQL Server:
- A
BITvalue is a PostgreSQLboolean. A cast to text givestrueorfalse, andCONCATgivestorf. SQL Server gives1or0in both. CAST('yes' AS bit)gives true, because PostgreSQL acceptsyes,onandt. SQL Server raises a conversion error.- A
DATETIMEvalue keeps every digit you give it, up to 6: SQL Server rounds it to 1/300 of a second. A string with more than 3 fractional digits, such as'2024-01-01 10:00:00.1234567', works in plx and fails in SQL Server. - A
DATETIMEbefore 1753, such as'1700-01-01', works in plx. SQL Server raises an out-of-range error. CAST(12 AS nvarchar(1))gives1. SQL Server raises error 8115, an arithmetic overflow.- A
UNIQUEIDENTIFIERturns into lowercase text. SQL Server gives uppercase. CASTof a string toVARBINARY(MAX)takes\x41as hex, soDATALENGTH(CAST('\x41' AS varbinary(max)))gives 1. SQL Server gives 4.CAST(N'ab' AS varbinary(max))holds the UTF-8 bytes, soDATALENGTHgives 2. SQL Server stores UTF-16 and gives 4.
Conversion to an integer type
SQL Server truncates a decimal or float value toward zero when it converts it to an integer type. A PostgreSQL cast rounds. plx truncates in these places:
CAST(e AS int), and the same withINTEGER,BIGINTandSMALLINT.CONVERT(int, e), and the same withINTEGER,BIGINT,SMALLINTandTINYINT.- An assignment to a variable of one of these types (
DECLARE @i int = e,SET @i = e,SET @i += e,SELECT @i = e).
So CAST(2.7 AS int) gives 2 and CONVERT(int, -2.5) gives -2, where
PostgreSQL gives 3 and -3. A string still converts as before:
CAST('1.5' AS int) fails, as in SQL Server.
When plx can see that the value is an integer, it emits a plain cast. When the
value is a decimal or float literal or variable, it emits trunc(). Otherwise,
such as for a column or a function call, it emits a call to plx_tsql_int4
(plx_tsql_int2 for SMALLINT and TINYINT, plx_tsql_int8 for
BIGINT). The extension installs these functions. Each one evaluates the
value once and checks its type when the function runs: it truncates a
numeric, real or double precision value, or a value of a domain over
one of these, and casts any other value. A T-SQL alias type created as a
PostgreSQL domain, such as CREATE DOMAIN price AS numeric(10,2), so
truncates too. The
call works wherever the cast does, such as around an aggregate or a window
function, or in a DEFAULT or CHECK of a table the function creates.
A conversion to a decimal type with fewer fractional digits rounds, in plx and
in SQL Server: CONVERT(decimal, 2.5) gives 3.
These places still round, because the conversion happens in PostgreSQL, not in the function body:
RETURN efrom a function that returns an integer type:RETURN 2.7gives- Write
RETURN CAST(e AS int).
- Write
INSERTandUPDATEof a decimal value into an integer column. WriteCAST(e AS int)in the statement.
Operators
| T-SQL | PostgreSQL |
|---|---|
a ^ b (bitwise exclusive OR) |
a # b |
~a (bitwise NOT) |
(~a) |
a != b |
a <> b |
@s + 'x' (every operand a known string) |
s || 'x' |
In PostgreSQL, ^ raises a number to a power, so plx always rewrites the T-SQL
^ to #. Use POWER(a, b) for exponentiation.
The bitwise operators ^, & and | bind differently. T-SQL gives them the
same precedence as + and - and evaluates left to right. PostgreSQL binds
#, & and | more loosely than + and -. plx does not add parentheses.
So 1 ^ 2 + 3 gives 6 in SQL Server, but 4 in plx, which computes
1 # (2 + 3). Put parentheses around a bitwise operation that is followed by
+ or -: write (1 ^ 2) + 3.
T-SQL binds the unary ~ more tightly than any other operator, and PostgreSQL
binds it more loosely than + and *. plx puts parentheses around ~ and its
operand, so ~1 + 2 gives 0, as in SQL Server.
String concatenation with +
T-SQL uses + both to add numbers and to join strings. PostgreSQL has no +
for strings: it joins them with ||. plx has no type information for most
expressions, so it decides from what it can see in the body.
plx writes + as || only when every operand of the chain is a known string.
A chain is a run of operands joined by +, such as @a + ' ' + @b. A known
string is one of:
- a variable or parameter declared with a variable-length string type:
VARCHAR,NVARCHAR,TEXTorNTEXT, with or without a length orMAX(CHARACTER VARYINGand the other names for them count too); - a string literal,
'...'orN'...'; CASTorCONVERTto one of those types;UPPER,LOWER,LTRIM,RTRIM,TRIM,SUBSTRING,LEFT,RIGHT,REPLACE,REPLICATE,CONCATorSTUFF;ISNULLwhose first argument is a string variable or a string literal;COALESCEwhose every argument is a known string.COALESCE('1', 2)is not one: SQL Server and PostgreSQL both give it the type of the number, soCOALESCE('1', 2) + '3'adds and gives 4.
SET @s += e becomes s := s || (e) only when @s is a string variable and
e is a known string or a chain of them.
In every other case plx keeps +. This includes a chain with a number, a
column, a parenthesized expression, a CASE expression, a BIT variable, a
CHAR or NCHAR value, or a function not in the list above, such as LEN. It also includes a chain followed
by -, &, | or ^, because PostgreSQL would group that operator with the
last operand. What happens then depends on the operands:
- A string variable next to anything that is not a known string fails when the
function runs:
@s + LEN(@s),@s + @nwith@nanINT, andSET @s += 1raiseoperator does not exist: character varying + integer. SQL Server converts the string to a number and adds. Write the conversion yourself, for exampleCAST(@s AS int) + LEN(@s). - A string literal next to a number adds, as in SQL Server:
'1' + 2is 3.
plx never guesses || from one string operand, because a wrong guess returns a
wrong value without an error. A failing call shows you where to add a CAST.
CREATE FUNCTION label(id int) RETURNS text LANGUAGE plxtsql AS $$
DECLARE @s varchar(40) = 'item';
SET @s += ' ';
RETURN @s + CONVERT(varchar(10), @id) + '/' + UPPER(@s);
$$;
Both + and || return NULL when either side is NULL, as in SQL Server with
the default CONCAT_NULL_YIELDS_NULL ON.
Write || or CONCAT(...) yourself to join strings that are not all known
strings, for example two columns, or a column and a literal.
CHAR(n) and NCHAR(n) values are not known strings, and neither are
CHARACTER(n) and NATIONAL CHARACTER(n). This covers a variable,
a parameter, and CAST or CONVERT to CHAR or NCHAR, with or without a
length. SQL Server keeps their trailing spaces when it joins them, but
PostgreSQL’s || drops them, so || would return a different value without an
error. plx keeps +, and the call fails:
CAST('a' AS char(3)) + 'b' raises
operator does not exist: character + unknown. Say what you want instead:
RTRIM(@c) + 'b'joins without the padding and gives the same value in both systems.rpad(@c, 3) || 'b'keeps the padding, as SQL Server does.
Assigning a value longer than n to a CHAR(n) variable is an error
(value too long for type character(3)). SQL Server cuts the value to n.
Dynamic SQL
EXEC('<sql>') (equivalently EXECUTE('<sql>')) becomes EXECUTE '<sql>';.
CREATE FUNCTION run(tbl text) RETURNS void LANGUAGE plxtsql AS $$
EXEC('TRUNCATE ' || @tbl);
$$;
Set-returning functions
A bare SELECT (not an assignment) in a function declared RETURNS TABLE(...)
or RETURNS SETOF ... becomes RETURN QUERY SELECT ....
CREATE FUNCTION series(n int) RETURNS TABLE(k int) LANGUAGE plxtsql AS $$
SELECT g FROM generate_series(1, @n) AS g;
$$;
In a scalar function a bare SELECT becomes PERFORM (evaluated for effect).
Statement separators
End statements with ;. T-SQL treats ; as optional in many places, but plx
uses it (together with block boundaries such as END, ELSE, and END TRY) to
find statement boundaries, so a missing ; between two statements can cause them
to be read as one. Control-flow blocks (IF, WHILE, BEGIN ... END,
TRY/CATCH) are self-delimiting and do not require a trailing ;.
Triggers
A function that returns trigger can back a trigger. Read the row through NEW
(and OLD on updates and deletes), and assign to a field with SET NEW.col = e,
which becomes NEW.col := e. Return NEW.
CREATE FUNCTION stamp() RETURNS trigger LANGUAGE plxtsql AS $$
SET NEW.total = NEW.qty * NEW.price;
SET NEW.tag = 'row ' || CONVERT(varchar, NEW.id);
RETURN NEW;
$$;
Session SET options
Statements such as SET NOCOUNT ON and SET XACT_ABORT ON are recognized and
ignored (they configure the SQL Server session and have no PostgreSQL
equivalent). SET @variable = ... and SET NEW.col = ... are always
assignments; a SET with no qualified target and no assignment is treated as a
session option.
Not supported
@@ROWCOUNT,@@ERROR, and other@@globals (except@@IDENTITY). UseGET DIAGNOSTICSor an exception handler in a construct plx does map, or write that part in another dialect.- Table variables (
DECLARE @t TABLE (...)) and T-SQL cursors (DECLARE c CURSOR). GOTO,WAITFOR, and label targets.- Transaction control (
BEGIN TRAN,COMMIT,ROLLBACK), in a function or a procedure; plxtsql rejects them everywhere. plxruby, plxphp, plxjs, plxts, plxpython3, plxcobol and plxplsql supportCOMMITandROLLBACKin a procedure; plxgo does not. - Calling a stored procedure by name (
EXEC procname); onlyEXEC('<sql>')dynamic SQL is supported. - A
SELECT @v = ... FROM ...assignment list that reads a variable it assigns, such as the accumulate idiomSELECT @t = @t + v FROM t. plx cannot run the expression once per row with the updated value, so it rejects the function rather than return a different result. Use an aggregate such asSUM(v)orstring_agg(name, ','). - String concatenation with
+when an operand is not a known string. See String concatenation with+.
See PARITY.md for the full per-dialect feature matrix and USERGUIDE.md for cross-dialect examples.