T-SQL vs PL/pgSQL: Converting Procedural Code
On this page
- A T-SQL cursor script translated to PL/pgSQL
- DO blocks run ad hoc code
- Variables live in a DECLARE section
- IF needs THEN and END IF
- FOR loops replace most cursors
- EXCEPTION blocks replace TRY/CATCH
- RAISE replaces PRINT and THROW
- Temporary tables: #temp becomes CREATE TEMP TABLE
- Dynamic SQL: sp_executesql becomes EXECUTE ... USING
- Check yourself
A T-SQL cursor script translated to PL/pgSQL
The fastest way to learn PL/pgSQL, PostgreSQL's procedural language, is to translate something you'd write in T-SQL. Here's a typical script: walk through open orders with a cursor, classify each one and keep going if one row fails.
-- T-SQL
DECLARE @OrderId INT, @OrderTotal DECIMAL(10,2);
DECLARE @Threshold DECIMAL(10,2) = 500.00;
DECLARE order_cursor CURSOR FOR
SELECT order_id, order_total FROM orders WHERE status = 'OPEN';
OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @OrderId, @OrderTotal;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
IF @OrderTotal > @Threshold
PRINT 'Order ' + CAST(@OrderId AS VARCHAR) + ' is high value';
ELSE IF @OrderTotal > 0
PRINT 'Order ' + CAST(@OrderId AS VARCHAR) + ' is normal';
ELSE
PRINT 'Order ' + CAST(@OrderId AS VARCHAR) + ' has no total';
END TRY
BEGIN CATCH
PRINT 'Error on order ' + CAST(@OrderId AS VARCHAR) + ': ' + ERROR_MESSAGE();
END CATCH
FETCH NEXT FROM order_cursor INTO @OrderId, @OrderTotal;
END;
CLOSE order_cursor;
DEALLOCATE order_cursor;The same logic in PL/pgSQL:
DO $$
DECLARE
v_threshold numeric(10,2) := 500.00;
r record;
BEGIN
FOR r IN SELECT order_id, order_total FROM orders WHERE status = 'OPEN' LOOP
BEGIN
IF r.order_total > v_threshold THEN
RAISE NOTICE 'Order % is high value', r.order_id;
ELSIF r.order_total > 0 THEN
RAISE NOTICE 'Order % is normal', r.order_id;
ELSE
RAISE NOTICE 'Order % has no total', r.order_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Error on order %: %', r.order_id, SQLERRM;
END;
END LOOP;
END;
$$;It's shorter, and every difference in it is a rule you'll use constantly. The rest of this lesson goes through them.
DO blocks run ad hoc code
In SSMS you can run a batch with variables and IF statements directly. In PostgreSQL, plain SQL can't contain variables or control flow. Procedural code lives in a function, a procedure (Day 9), or for one-off scripts a DO block: DO $$ ... $$;.
The $$ marks are dollar quoting. Everything between them is one string, so single quotes inside don't need escaping. A DO block takes no parameters and returns nothing, which makes it right for data fixes and experiments.
Variables live in a DECLARE section
| T-SQL | PL/pgSQL |
|---|---|
DECLARE @x INT = 5; anywhere in the batch | x int := 5; in the DECLARE section, before BEGIN |
@ prefix | No prefix |
SET @x = @x + 1; | x := x + 1; |
SELECT @x = col FROM ... | SELECT col INTO x FROM ...; |
| No constants | x CONSTANT int := 5; |
Without the @, a variable can share a name with a column, and then PostgreSQL raises an "ambiguous" error. That's why the v_ prefix is a common convention. It isn't required, but it saves a lot of confusion.
IF needs THEN and END IF
PL/pgSQL closes every block explicitly. The two changes you'll forget at first: it's ELSIF, one word, and every IF ends with END IF;. T-SQL's habit of wrapping branches in BEGIN/END isn't needed.
FOR loops replace most cursors
T-SQL has no FOR loop, so looping over rows means DECLARE CURSOR, OPEN, FETCH, @@FETCH_STATUS, CLOSE and DEALLOCATE. PL/pgSQL's FOR r IN <query> LOOP does all of that. Each row lands in the record variable r, and the cursor is closed when the loop ends, even on error. There are also number ranges and WHILE loops:
DO $$
DECLARE
v_counter int := 0;
BEGIN
FOR i IN 1..3 LOOP
RAISE NOTICE 'Iteration %', i;
END LOOP;
WHILE v_counter < 3 LOOP
v_counter := v_counter + 1;
END LOOP;
END;
$$;The same advice applies as in SQL Server. If a loop updates rows one at a time, a single set-based UPDATE is usually far faster. Migration is a good moment to replace cursors that only exist because "that's how it was written".
EXCEPTION blocks replace TRY/CATCH
| T-SQL | PL/pgSQL |
|---|---|
BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH | BEGIN ... EXCEPTION WHEN ... THEN ... END; |
Check ERROR_NUMBER() inside CATCH | WHEN unique_violation THEN, WHEN division_by_zero THEN |
ERROR_MESSAGE(), ERROR_NUMBER() | SQLERRM, SQLSTATE |
THROW; to rethrow | RAISE; |
Named conditions make handlers readable. You catch unique_violation instead of comparing with error 2627:
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO accounts VALUES (1, 100);
DO $$
BEGIN
INSERT INTO accounts (id, balance) VALUES (1, 100);
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'Account already exists: %', SQLERRM;
END;
$$;One difference matters for correctness. When an EXCEPTION block catches an error, PostgreSQL rolls back everything that block did, as if a savepoint had been set at its BEGIN. A T-SQL CATCH doesn't undo earlier statements by itself. That's why the translated script puts its BEGIN/EXCEPTION inside the loop: a failing row undoes only its own work. Each block with an EXCEPTION clause has a small cost, so keep them tight.
RAISE replaces PRINT and THROW
| Purpose | T-SQL | PL/pgSQL |
|---|---|---|
| Debug message | PRINT 'text' | RAISE NOTICE 'text'; |
| Warning | RAISERROR('text', 10, 1) | RAISE WARNING 'text'; |
| Error that stops execution | THROW 50001, 'text', 1; | RAISE EXCEPTION 'text'; |
RAISE fills % placeholders from the arguments that follow, converting types for you, so the CAST(@x AS VARCHAR) noise disappears. In DataGrip, NOTICE messages appear in the output pane of the console, like the Messages tab in SSMS.
Temporary tables: #temp becomes CREATE TEMP TABLE
Scripts that stage rows in #temp tables translate directly:
-- T-SQL: SELECT ... INTO #big_orders FROM orders WHERE order_total > 100;
CREATE TEMP TABLE big_orders AS
SELECT order_id, customer_id, order_total FROM orders WHERE order_total > 100;
BEGIN;
CREATE TEMP TABLE batch_ids (id int PRIMARY KEY) ON COMMIT DROP; -- gone at COMMIT
INSERT INTO batch_ids VALUES (10), (12);
COMMIT;A temporary table is visible only to the session that created it, including any functions that session calls, and it's dropped when the session ends. ON COMMIT DROP ties it to one transaction instead, which is the safer choice behind a transaction-mode connection pooler (Day 20). Two differences catch people out:
- No table variables.
DECLARE @t TABLE (...)has no equivalent. Use a temp table, or an array variable for a short list of ids. - Autovacuum can't see temp tables. If you load thousands of rows into one and then join against it, run
ANALYZE big_orders;first, or the planner guesses its size.
Dynamic SQL: sp_executesql becomes EXECUTE ... USING
T-SQL builds SQL strings and runs them with sp_executesql. PL/pgSQL uses EXECUTE, with format() for identifiers and USING for values:
CREATE FUNCTION count_rows(p_table text, p_min numeric)
RETURNS bigint
LANGUAGE plpgsql STABLE
AS $$
DECLARE
v_count bigint;
BEGIN
EXECUTE format('SELECT count(*) FROM %I WHERE order_total > $1', p_table)
INTO v_count
USING p_min;
RETURN v_count;
END;
$$;
SELECT count_rows('orders', 50);%I quotes an identifier safely, like QUOTENAME(), so a table name with spaces or capitals becomes "Order Lines". %L quotes a literal value. USING p_min passes the value as a parameter ($1), like the parameter list of sp_executesql, so it's never pasted into the SQL text at all. Never build dynamic SQL by joining strings with ||. That's how SQL injection gets into migrated code.
Check yourself
A missing END IF;, or ELSE IF written as two words. PL/pgSQL closes each IF with END IF and spells the middle branch ELSIF.
Catching an exception rolls back everything done inside that BEGIN/EXCEPTION block. Put the block around only the step that may fail, or record the failure after the block.
Skip the loop entirely: UPDATE products SET price = price * 1.10;. Row-by-row loops are slow in both databases, and migration is a good time to make them set-based.
RETURN QUERY EXECUTE format('SELECT * FROM %I WHERE id = $1', p_table_name) USING p_id;. %I quotes the table name, and USING passes the id as a parameter instead of text.
RAISE NOTICE 'Processing %', v_name;. The % placeholders are filled from the following arguments in order. Use RAISE EXCEPTION instead of THROW to abort with an error, optionally with USING ERRCODE to set a specific SQLSTATE.