Transact-SQL cookbook¶
Practical recipes for the plxtsql dialect (SQL Server / Sybase T-SQL). Every recipe here was run on PostgreSQL; plx transpiles the body to plpgsql and the standard interpreter executes it. See the plxtsql chapter for the full language reference.
Scalar function with branching¶
Declare a local with DECLARE @g varchar and pick a value through an IF / ELSE IF / ELSE chain. There is no THEN keyword in T-SQL: the condition runs up to the start of the body. A single-statement branch needs no BEGIN ... END.
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 IF @score >= 70
SET @g = 'C';
ELSE
SET @g = 'F';
RETURN @g;
$$;
Accumulating loop¶
A WHILE loop with a BEGIN ... END body. DECLARE @x int = 0 both declares and initializes; SET @total += @i is the compound-assignment form, which expands to total := total + (i).
CREATE FUNCTION sum_to(n int) RETURNS bigint LANGUAGE plxtsql AS $$
DECLARE @i int = 1;
DECLARE @total bigint = 0;
WHILE @i <= @n
BEGIN
SET @total += @i;
SET @i += 1;
END
RETURN @total;
$$;
Building a text result in a loop¶
T-SQL overloads + for both numeric addition and string concatenation, so plx leaves + untranslated. Use || or CONCAT(...) to join strings. VARCHAR(MAX) maps to text.
CREATE FUNCTION repeat_word(word text, times int) RETURNS text LANGUAGE plxtsql AS $$
DECLARE @out varchar(max) = '';
DECLARE @i int = 1;
WHILE @i <= @times
BEGIN
IF @i > 1
SET @out = @out || ', ';
SET @out = CONCAT(@out, @word);
SET @i += 1;
END
RETURN @out;
$$;
Reading a value from a query¶
SELECT @x = col FROM t ... becomes SELECT col INTO x FROM t .... This is the T-SQL idiom for pulling a computed or aggregated value out of a table into a local variable. ISNULL(@total, 0) guards against a group with no rows.
CREATE TABLE orders (id int, grp int, amount int);
INSERT INTO orders VALUES (1,1,10),(2,1,25),(3,1,5),(4,2,100);
CREATE FUNCTION order_total(g int) RETURNS bigint LANGUAGE plxtsql AS $$
DECLARE @total bigint;
SELECT @total = SUM(amount) FROM orders WHERE grp = @g;
RETURN ISNULL(@total, 0);
$$;
Name a local so it does not collide with a column referenced in the same query. If a variable and a column share a name, PostgreSQL reports an ambiguous reference.
Set-returning function¶
In a function declared RETURNS TABLE(...) or RETURNS SETOF ..., a bare SELECT (one that is not an assignment) becomes RETURN QUERY SELECT ....
CREATE FUNCTION squares(n int) RETURNS TABLE(k int) LANGUAGE plxtsql AS $$
SELECT g * g FROM generate_series(1, @n) AS g;
$$;
Error handling with TRY / CATCH¶
BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH becomes a plpgsql block with an EXCEPTION WHEN OTHERS handler. ERROR_MESSAGE() maps to SQLERRM, so the caught message is available in the handler.
CREATE FUNCTION safe_divide(a int, b int) RETURNS text LANGUAGE plxtsql AS $$
DECLARE @result varchar(100);
BEGIN TRY
SET @result = CAST(@a / @b AS text);
END TRY
BEGIN CATCH
SET @result = 'error: ' || ERROR_MESSAGE();
END CATCH
RETURN @result;
$$;
Trigger function¶
A trigger function returns trigger and reads the row through NEW (and OLD on updates and deletes). Assign to a field with SET NEW.col = e, which becomes NEW.col := e. This BEFORE INSERT trigger validates the incoming row with THROW, then derives a fee and a tag before the row is stored.
CREATE TABLE deposits (id int, amount numeric, fee numeric, tag text);
CREATE FUNCTION deposits_stamp() RETURNS trigger LANGUAGE plxtsql AS $$
IF NEW.amount < 0
THROW 50001, 'amount cannot be negative', 1;
SET NEW.fee = NEW.amount * 0.02;
SET NEW.tag = 'deposit ' || CONVERT(varchar, NEW.id);
RETURN NEW;
$$;
CREATE TRIGGER trg_deposits BEFORE INSERT ON deposits
FOR EACH ROW EXECUTE FUNCTION deposits_stamp();
INSERT INTO deposits (id, amount) VALUES (1, 100), (2, 50);
SELECT id, amount, fee, tag FROM deposits ORDER BY id;
id | amount | fee | tag
----+--------+------+-----------
1 | 100 | 2.00 | deposit 1
2 | 50 | 1.00 | deposit 2
A row that fails the check is rejected:
Dynamic SQL¶
EXEC('<sql>') (or EXECUTE('<sql>')) becomes plpgsql EXECUTE '<sql>';. Build the statement text with ||.
CREATE FUNCTION make_seed(tbl text) RETURNS void LANGUAGE plxtsql AS $$
EXEC('CREATE TABLE ' || @tbl || ' (id int)');
EXEC('INSERT INTO ' || @tbl || ' VALUES (1), (2), (3)');
$$;
T-SQL scalar functions¶
LEN, CHARINDEX, IIF, CONVERT, and ISNULL are rewritten to their PostgreSQL equivalents: length, strpos (with arguments swapped), a CASE expression, CAST, and coalesce. CHARINDEX returns 0 when the substring is not found.
CREATE FUNCTION describe(s text) RETURNS text LANGUAGE plxtsql AS $$
DECLARE @n int = LEN(@s);
DECLARE @pos int = CHARINDEX('@', @s);
DECLARE @kind varchar(20) = IIF(@pos > 0, 'email', 'plain');
RETURN CONCAT('len=', CONVERT(varchar, @n),
' at=', CONVERT(varchar, @pos),
' kind=', @kind,
' safe=', ISNULL(@s, 'null'));
$$;
describe | describe
-------------------------------------+----------------------------------
len=8 at=4 kind=email safe=bob@x.io | len=5 at=0 kind=plain safe=plain
Last inserted identity and multi-target assignment¶
@@IDENTITY maps to lastval(), so it reports the value the surrounding INSERT generated for a serial column. SELECT @a = ..., @b = ... FROM t assigns several targets from one row in a single statement.
CREATE TABLE people (id serial primary key, name text, age int);
CREATE FUNCTION add_person(pname text, page int) RETURNS text LANGUAGE plxtsql AS $$
DECLARE @new_id int;
DECLARE @who varchar(50);
DECLARE @yrs varchar(10);
INSERT INTO people(name, age) VALUES (@pname, @page);
SET @new_id = @@IDENTITY;
SELECT @who = name, @yrs = CAST(age AS text) FROM people WHERE id = @new_id;
RETURN CONCAT('id=', CAST(@new_id AS text), ' ', @who, ' age ', @yrs);
$$;