Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 2 · Lesson 8 · 7 min read

T-SQL vs PL/pgSQL: Converting Procedural Code

On this page
  1. A T-SQL cursor script translated to PL/pgSQL
  2. DO blocks run ad hoc code
  3. Variables live in a DECLARE section
  4. IF needs THEN and END IF
  5. FOR loops replace most cursors
  6. EXCEPTION blocks replace TRY/CATCH
  7. RAISE replaces PRINT and THROW
  8. Temporary tables: #temp becomes CREATE TEMP TABLE
  9. Dynamic SQL: sp_executesql becomes EXECUTE ... USING
  10. 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.

SQL
-- 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:

SQL
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-SQLPL/pgSQL
DECLARE @x INT = 5; anywhere in the batchx int := 5; in the DECLARE section, before BEGIN
@ prefixNo prefix
SET @x = @x + 1;x := x + 1;
SELECT @x = col FROM ...SELECT col INTO x FROM ...;
No constantsx 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:

Code
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-SQLPL/pgSQL
BEGIN TRY ... END TRY BEGIN CATCH ... END CATCHBEGIN ... EXCEPTION WHEN ... THEN ... END;
Check ERROR_NUMBER() inside CATCHWHEN unique_violation THEN, WHEN division_by_zero THEN
ERROR_MESSAGE(), ERROR_NUMBER()SQLERRM, SQLSTATE
THROW; to rethrowRAISE;

Named conditions make handlers readable. You catch unique_violation instead of comparing with error 2627:

SQL
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

PurposeT-SQLPL/pgSQL
Debug messagePRINT 'text'RAISE NOTICE 'text';
WarningRAISERROR('text', 10, 1)RAISE WARNING 'text';
Error that stops executionTHROW 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:

SQL
-- 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:

SQL
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.