# 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 ```sql 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. ```sql 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 as `orders` or `Orders` without 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 as `name`. ### 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](#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](#a-catch-block-undoes-the-writes-made-in-try)) | | `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. ```sql 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: 1. Move a write that must survive, such as an audit or error-log row, before `BEGIN TRY`. 2. Or make it in the `CATCH` block, which runs after the rollback. To use a value computed in `TRY`, 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 `BIT` value is a PostgreSQL `boolean`. A cast to text gives `true` or `false`, and `CONCAT` gives `t` or `f`. SQL Server gives `1` or `0` in both. - `CAST('yes' AS bit)` gives true, because PostgreSQL accepts `yes`, `on` and `t`. SQL Server raises a conversion error. - A `DATETIME` value 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 `DATETIME` before 1753, such as `'1700-01-01'`, works in plx. SQL Server raises an out-of-range error. - `CAST(12 AS nvarchar(1))` gives `1`. SQL Server raises error 8115, an arithmetic overflow. - A `UNIQUEIDENTIFIER` turns into lowercase text. SQL Server gives uppercase. - `CAST` of a string to `VARBINARY(MAX)` takes `\x41` as hex, so `DATALENGTH(CAST('\x41' AS varbinary(max)))` gives 1. SQL Server gives 4. - `CAST(N'ab' AS varbinary(max))` holds the UTF-8 bytes, so `DATALENGTH` gives 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 with `INTEGER`, `BIGINT` and `SMALLINT`. - `CONVERT(int, e)`, and the same with `INTEGER`, `BIGINT`, `SMALLINT` and `TINYINT`. - 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 e` from a function that returns an integer type: `RETURN 2.7` gives 3. Write `RETURN CAST(e AS int)`. - `INSERT` and `UPDATE` of a decimal value into an integer column. Write `CAST(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`, `TEXT` or `NTEXT`, with or without a length or `MAX` (`CHARACTER VARYING` and the other names for them count too); - a string literal, `'...'` or `N'...'`; - `CAST` or `CONVERT` to one of those types; - `UPPER`, `LOWER`, `LTRIM`, `RTRIM`, `TRIM`, `SUBSTRING`, `LEFT`, `RIGHT`, `REPLACE`, `REPLICATE`, `CONCAT` or `STUFF`; - `ISNULL` whose first argument is a string variable or a string literal; - `COALESCE` whose every argument is a known string. `COALESCE('1', 2)` is not one: SQL Server and PostgreSQL both give it the type of the number, so `COALESCE('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 + @n` with `@n` an `INT`, and `SET @s += 1` raise `operator does not exist: character varying + integer`. SQL Server converts the string to a number and adds. Write the conversion yourself, for example `CAST(@s AS int) + LEN(@s)`. - A string literal next to a number adds, as in SQL Server: `'1' + 2` is 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`. ```sql 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('')` (equivalently `EXECUTE('')`) 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 ...`. ```sql 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`. ```sql 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`). Use `GET DIAGNOSTICS` or 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 support `COMMIT` and `ROLLBACK` in a procedure; plxgo does not. - Calling a stored procedure by name (`EXEC procname`); only `EXEC('')` dynamic SQL is supported. - A `SELECT @v = ... FROM ...` assignment list that reads a variable it assigns, such as the accumulate idiom `SELECT @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 as `SUM(v)` or `string_agg(name, ',')`. - String concatenation with `+` when an operand is not a known string. See [String concatenation with `+`](#string-concatenation-with). See [PARITY.md](PARITY.md) for the full per-dialect feature matrix and [USERGUIDE.md](USERGUIDE.md) for cross-dialect examples.