Porting T-SQL to PL/pgSQL — Migrating Procedures and Functions

Published Jun 27, 2026
by

The data is over, the schema stands — and then there are 200 stored procedures sitting there that no tool translates for you. pgloader migrates tables and data, but the logic in procedures, functions, and triggers stays behind. This is the phase that’s actually work: porting T-SQL to PL/pgSQL, line by line, with understanding instead of search-and-replace.

The good news: most of it is mechanical. A few dozen idioms (ISNULL→COALESCE, TOP→LIMIT, @variable→l_variable) cover the majority, and the structural differences are quickly grasped. This article is the reference for that — plus the honest list of the spots that demand manual work and an eye for detail.

The essentials up front:

  • Structure first: CREATE FUNCTION/PROCEDURE with $$ quoting, the DECLARE block moves to the top, the @ before variables is gone.
  • The idiom table translates the most common functions and constructs 1:1 — the mechanical 80%.
  • Error handling: TRY/CATCH becomes BEGIN … EXCEPTION WHEN … END, THROW becomes RAISE — but often you don’t need it at all, because PL/pgSQL propagates errors on its own.
  • Dynamic SQL is the security-critical spot: sp_executesql becomes EXECUTE format(...) with %I/%L.

Prerequisite: SQL Server as the source, PostgreSQL 14+ as the target. T-SQL constructs (TRY/CATCH, @variable, TOP, ISNULL, sp_executesql, temp tables #t) are assumed; the PL/pgSQL counterparts are shown. What clean PL/pgSQL code looks like (volatility, RETURNS, naming conventions) is its own topic — the convention articles on procedures and functions are linked; here it’s about the pure translation.

Contents

From T-SQL to PL/pgSQL: the Structural Differences

Before the individual functions, the frame. A T-SQL procedure and a PL/pgSQL routine look similar at first glance — BEGIN, END, DECLARE — but four things are different, and they affect every ported routine.

This is what a T-SQL procedure looks like in skeleton form:

  1: CREATE PROCEDURE dbo.usp_name
  2:     @p_param int
  3: AS
  4: BEGIN
  5:    DECLARE @l_var int;
  6:    -- logic
  7: END;

And the same as PL/pgSQL:

  1: CREATE OR REPLACE FUNCTION fn_name
  2: (
  3:     IN    p_param   integer
  4: )
  5: RETURNS integer
  6: LANGUAGE plpgsql
  7: AS $function$
  8: DECLARE
  9:    l_var   integer;
 10: BEGIN
 11:    -- logic
 12:    RETURN l_var;
 13: END;
 14: $function$;

The four differences:

  • The body sits in dollar quotes. The function body is passed as a dollar-quoted string to CREATE FUNCTION and compiled by the PL/pgSQL handler at creation time — not executed as text at runtime. That’s why the whole body sits between $function$ … $function$ (or $procedure$ for procedures); the dollar quoting saves escaping single quotes inside the code.
  • DECLARE goes at the top, once. T-SQL allows DECLARE anywhere; PL/pgSQL collects all local variables in one DECLARE block right before BEGIN. The @ before variables and parameters drops entirely — @l_var becomes l_var, @p_param becomes p_param.
  • PROCEDURE or FUNCTION? This is the first real decision. Postgres has both: a FUNCTION returns a value (or a table) and is the normal case for anything that yields a result. A PROCEDURE (since PostgreSQL 11) returns nothing but may run COMMIT/ROLLBACK — it’s there for multi-step batch flows. A T-SQL procedure that returns a value via SELECT therefore usually becomes a FUNCTION, not a PROCEDURE — and one that returns a whole result set becomes a FUNCTION with RETURNS TABLE/SETOF (details in the table-returning article below).
  • Names move straight into the target style. When porting, the code lands directly in snake_case — you don’t drag the SQL Server PascalCase names (usp_AddCustomer) along. The fn_/sp_ prefixes in the examples are this blog’s convention, not a PostgreSQL mandate; many projects deliberately skip prefixes and use plain snake_case. Either way: the right moment to switch is now, not later.

SET NOCOUNT ON and the like have no counterpart and simply drop — Postgres sends no “rows affected” interim messages that you’d have to suppress.

The Idiom Translation Table

The following table is the actual reference — the most common T-SQL constructs and their PL/pgSQL counterpart:

T-SQLPL/pgSQLNote
ISNULL(x, y)COALESCE(x, y)COALESCE is SQL-standard and n-ary; different return type than ISNULL — see below
GETDATE()now() / current_timestampnow() = current_timestamp, but the transaction start time (constant per transaction); for the time at the call, clock_timestamp()
GETUTCDATE()now() AT TIME ZONE 'utc'UTC time as timestamp without time zone (zone-less)
DATEADD(day, 1, @d)@d + interval '1 day'date + int counts days; timestamp + int is an error — see below
DATEDIFF(day, a, b)(b::date - a::date)for day ≈ date boundaries; for month/year, DATEDIFF counts boundaries — use age()/extract, not a plain subtraction
LEN(s)length(s)both count characters; LEN ignores trailing spaces, length doesn’t — use length(rtrim(s)) if needed
DATALENGTH(s)octet_length(s)bytes instead of characters; SQL Server nvarchar is UTF-16 (2 bytes/char), Postgres UTF-8 — byte counts diverge for non-ASCII
SUBSTRING(s, 1, 3)substring(s, 1, 3)both 1-based
CHARINDEX('x', s)position('x' IN s)both 0 when not found
a + b (strings)a || b+ is arithmetic-only in Postgres; for NULL-tolerant concatenation, concat(a, b)
TOP (n)LIMIT ngoes at the end of the SELECT in Postgres
IIF(c, a, b)CASE WHEN c THEN a ELSE b END–
@variablel_variable (in DECLARE)no @, declared at the start of the block
@p … OUTPUT (parameter)OUT p / INOUT preturn via named OUT parameters instead of OUTPUT
EXEC sp · SELECT fn() (discard result)CALL sp() · PERFORM fn()a bare SELECT without INTO is an error in PL/pgSQL — PERFORM discards the result
#tempCREATE TEMP TABLE or CTEoften a CTE is enough instead of a real temp table
OUTPUT inserted.idRETURNING iddirectly on the INSERT/UPDATE/DELETE
SCOPE_IDENTITY()RETURNING … INTOthe clean way; currval/lastval only with care
sp_executesqlEXECUTE format(…) USING …see its own section
TRY/CATCHBEGIN … EXCEPTION WHEN … ENDsee its own section
THROW / RAISERRORRAISE EXCEPTION … USING …see its own section

Three traps from the table deserve a sentence more. ISNULL vs. COALESCE: the two aren’t even type-equal in SQL Server. ISNULL(x, y) takes the data type of the first argument and truncates the second value to it — ISNULL(@v varchar(3), 'ABCDEF') yields 'ABC'. COALESCE, by contrast, follows type precedence across all arguments and doesn’t truncate ('ABCDEF'). The COALESCE ported to Postgres behaves like the SQL Server COALESCE (no truncation, verified here against Postgres 16) — if the source relies on the ISNULL truncation, the port changes the result. LEN vs. length: anyone doing a length check on a char(n) field in T-SQL often unconsciously relies on LEN stripping the trailing spaces — the naive port to length then changes the result. + vs. ||: a + accidentally carried over between two strings isn’t a typo in Postgres but a type error or — with numeric strings — a silently wrong result.

Behind this stands a fundamental stance that affects every ported computation: PostgreSQL converts far less implicitly in expressions than SQL Server. SQL Server converts generously between number and string ('1' + 1 yields 2, an int column against a varchar column is silently reconciled). Postgres does this only for untyped literals and throws an error on typed values — '1'::text + 1 is operator does not exist: text + integer.

Two consequences for porting: date arithmetic needs an interval — GETDATE() + 1 becomes now() + interval '1 day', not now() + 1 (which is an error in Postgres). And string/number mixes must be cast explicitly (::numeric, ::int).

Integer division, on the other hand, behaves the same in both systems (5 / 2 = 2) — nothing changes there when porting. The full column type mapping is covered by the data type article.

Error Handling: TRY/CATCH Becomes EXCEPTION

The translation looks mechanical at first: the BEGIN TRY … END TRY BEGIN CATCH … END CATCH of T-SQL becomes a BEGIN … EXCEPTION WHEN … END block in PL/pgSQL. THROW and RAISERROR become RAISE.

The more important point, though: you often don’t need the block at all. In T-SQL, TRY/CATCH is frequently only there to catch an error and immediately rethrow it (BEGIN CATCH THROW; END CATCH). PL/pgSQL propagates errors upward on its own — a block that only catches and rethrows is redundant and can disappear entirely. You add an EXCEPTION block only where you actually handle the error.

Here is what a real handler looks like — catching a duplicate email instead of letting the procedure abort:

  1: CREATE OR REPLACE FUNCTION fn_try_add_customer
  2: (
  3:     IN    p_email   text
  4: )
  5: RETURNS integer
  6: LANGUAGE plpgsql
  7: AS $function$
  8: DECLARE
  9:    l_new_id   integer;
 10: BEGIN
 11:
 12:    INSERT INTO customer (email)
 13:    VALUES (p_email)
 14:    RETURNING customer_id INTO l_new_id;
 15:
 16:    RETURN l_new_id;
 17:
 18: EXCEPTION
 19:    WHEN unique_violation THEN
 20:       RAISE NOTICE 'email % already exists', p_email;
 21:       RETURN NULL;
 22: END;
 23: $function$;

Three things that stand out when porting error handling:

  • Errors are caught by named codes, not numbers. Instead of the SQL Server error number (2627 for a key violation), PL/pgSQL catches via descriptive condition names like unique_violation or foreign_key_violation. WHEN OTHERS is the counterpart to the general CATCH.
  • THROW 50001, 'text', 1 becomes RAISE EXCEPTION 'text'. The message sits directly on the RAISE. The USING ERRCODE = '…' clause is optional — it’s worth it only when the error should be caught specifically via a SQLSTATE; normally a bare RAISE EXCEPTION 'text' is enough. For pure notices there’s RAISE NOTICE/RAISE WARNING — the counterpart to RAISERROR with a low severity.
  • ERROR_MESSAGE()/ERROR_NUMBER() become SQLERRM/SQLSTATE. Inside the EXCEPTION block, these two variables give the text and code of the caught error.

A performance note that’s easy to miss: every EXCEPTION block internally opens a subtransaction (a savepoint). That costs a little per call — in a tight loop with millions of iterations, an EXCEPTION block isn’t free. This fits the mindset from type conversion: wherever possible, the error-tolerant operation (a safe conversion, an ON CONFLICT) is the cleaner path than catching a thrown exception.

Porting Dynamic SQL Safely

Here it’s not just the syntax that’s decided, but the security. T-SQL builds dynamic SQL with sp_executesql and parameterizes via @params. PL/pgSQL uses EXECUTE together with format() — and format() has two placeholders that close exactly the injection gap that string concatenation tears open:

  • %I quotes an identifier (table, column name) correctly and safely.
  • %L quotes a literal; alternatively — and better for values — you bind parameters via USING and reference them as $1, $2.
  1: CREATE OR REPLACE FUNCTION fn_count_rows
  2: (
  3:     IN    p_schema_name   text
  4:    ,IN    p_table_name    text
  5:    ,IN    p_min_id        integer
  6: )
  7: RETURNS bigint
  8: LANGUAGE plpgsql
  9: AS $function$
 10: DECLARE
 11:    l_count   bigint;
 12: BEGIN
 13:
 14:    EXECUTE format($sql$SELECT count(*)
 15:                        FROM %I.%I
 16:                        WHERE customer_id >= $1
 17:                   $sql$
 18:       ,p_schema_name
 19:       ,p_table_name
 20:    )
 21:    INTO l_count
 22:    USING p_min_id;
 23:
 24:    RETURN l_count;
 25: END;
 26: $function$;

The pattern: insert identifiers via %I into the SQL string (at build time), bind values via $1 and USING (at execution time). Never glue an input value into the string via || — that’s the classic SQL injection spot. The trust boundary is thereby clearly drawn: structures (%I) are controlled, values (USING) are bound type-safely. The same format() pattern also carries the data quality framework that runs generic check rules via dynamic SQL — the cross-reference is below.

Before and After: the Whole Procedure

To bring it together, the running example: a T-SQL procedure that creates a customer, validates the email, and returns the new ID. First the source:

  1: CREATE PROCEDURE dbo.usp_add_customer
  2:     @p_email         nvarchar(256)
  3:    ,@p_credit_limit  money = 0
  4: AS
  5: BEGIN
  6:    SET NOCOUNT ON;
  7:    DECLARE @l_new_id int;
  8:
  9:    IF @p_email IS NULL OR LEN(@p_email) = 0
 10:       THROW 50001, 'email must not be empty', 1;
 11:
 12:    INSERT INTO dbo.customer (email, credit_limit)
 13:    VALUES (@p_email, ISNULL(@p_credit_limit, 0));
 14:
 15:    SET @l_new_id = SCOPE_IDENTITY();
 16:
 17:    SELECT @l_new_id AS new_customer_id;
 18: END;

And the PL/pgSQL target — because the routine returns a value, it becomes a FUNCTION:

  1: CREATE OR REPLACE FUNCTION fn_add_customer
  2: (
  3:     IN    p_email          text
  4:    ,IN    p_credit_limit   numeric(19, 4) DEFAULT 0
  5: )
  6: RETURNS integer
  7: LANGUAGE plpgsql
  8: AS $function$
  9: DECLARE
 10:    l_new_id   integer;
 11: BEGIN
 12:
 13:    IF p_email IS NULL OR length(p_email) = 0 THEN
 14:       RAISE EXCEPTION 'email must not be empty'
 15:          USING ERRCODE = 'check_violation';
 16:    END IF;
 17:
 18:    INSERT INTO customer (email, credit_limit)
 19:    VALUES (p_email, COALESCE(p_credit_limit, 0))
 20:    RETURNING customer_id INTO l_new_id;
 21:
 22:    RETURN l_new_id;
 23: END;
 24: $function$;

What changed line by line:

  • Signature: @p_email nvarchar(256) → p_email text, @p_credit_limit money = 0 → p_credit_limit numeric(19, 4) DEFAULT 0 (this is how money is usually replaced in migrations; the type mapping is covered by the data type article). The @ and the parenthesis syntax follow the PL/pgSQL form.
  • IF … LEN(…) = 0 → IF … length(…) = 0 THEN … END IF; — the THEN and the END IF; are mandatory in PL/pgSQL.
  • THROW → RAISE EXCEPTION … USING ERRCODE = ….
  • INSERT + SCOPE_IDENTITY() → INSERT … RETURNING customer_id INTO l_new_id — one step instead of two, without the SCOPE_IDENTITY() trap.
  • SELECT @l_new_id → RETURN l_new_id; — the function returns the value directly instead of sending it as a one-row result set.

What No Tool Does for You

The mechanical translation covers the majority. The “last 20%” are the spots where an automatic converter stumbles or simply gives up — and where it’s decided whether the ported code really does the same thing:

  • Cursor logic. T-SQL cursors can often be replaced by a set-based statement — almost always the better solution. Where a cursor has to stay, it becomes a FOR … IN … LOOP. That’s rethinking, not search-and-replace.
  • MERGE subtleties. Postgres knows MERGE (since version 15) and INSERT … ON CONFLICT. But ON CONFLICT does not fully replace MERGE — it only kicks in on unique/exclusion conflicts; for more general upsert logic, MERGE remains. The choice and the exact semantics (what happens on multiple matches) must be checked per statement.
  • Trigger functions. A T-SQL trigger becomes two objects in Postgres: a trigger function (the logic) and the trigger itself (the wiring to the table). inserted/deleted become NEW/OLD.
  • Transaction and error semantics — the biggest conceptual difference. If a routine in Postgres fails with an unhandled error, the entire transaction is rolled back — including the statements that already ran before the error (verified against Postgres 16: two INSERTs before a RAISE → 0 rows left). SQL Server does not necessarily do this: in the default (XACT_ABORT OFF), a runtime error often rolls back only the failing statement and keeps going — earlier statements persist; only SET XACT_ABORT ON or TRY/CATCH with an explicit ROLLBACK forces the Postgres-style atomicity. Anyone porting a T-SQL procedure that relies on this partial-success behavior gets a different result in Postgres. On top of that, the detail differences: no COMMIT in a FUNCTION (only in a PROCEDURE), and every EXCEPTION block is an implicit subtransaction. This whole topic is deep enough for an article of its own.
  • @@ROWCOUNT and diagnostics. What T-SQL gives you via @@ROWCOUNT, you fetch in PL/pgSQL via GET DIAGNOSTICS l_count = ROW_COUNT;.

This list isn’t cause for worry but a priority list: this is exactly where a second look pays off, while the idiom table handles the rest quickly.

FAQ

Does every T-SQL procedure become a Postgres PROCEDURE?

No. If the routine returns a value or a result set — which T-SQL procedures often do via SELECT — it usually becomes a FUNCTION in Postgres. A PROCEDURE (since PostgreSQL 11) returns nothing but may run COMMIT/ROLLBACK and is thus meant for multi-step batch flows. The decision is made by the return behavior, not by the source object type.

What replaces TRY/CATCH in PL/pgSQL?

A BEGIN … EXCEPTION WHEN condition THEN … END block. Important: PL/pgSQL propagates errors upward on its own — a TRY/CATCH that in T-SQL only catches and immediately rethrows is redundant and drops. You add an EXCEPTION block only where you actually handle the error; catching is by named conditions like unique_violation, not by error numbers.

How do I translate sp_executesql?

Via EXECUTE format(…) USING …. Identifiers (table, column names) you insert with %I, values you bind via USING as $1, $2. Never glue input values into the string via || — that’s the SQL injection spot. %I and USING draw the trust boundary cleanly: structure controlled, values bound type-safely.

Are there temp tables in Postgres?

Yes: CREATE TEMP TABLE creates a session-local table that vanishes at the end of the connection. Often you don’t need it at all, though — a WITH CTE or a derived table does the same inline and without the detour over a physical object. The #temp with the # prefix familiar from T-SQL doesn’t exist as such.

What becomes of SCOPE_IDENTITY()?

The RETURNING clause. Instead of reading back the assigned key with SCOPE_IDENTITY() after the INSERT, you append RETURNING customer_id INTO l_new_id directly to the INSERT — one step, without the ambiguities of SCOPE_IDENTITY()/@@IDENTITY. currval/lastval work too, but are more error-prone and only the second choice.

This article is part of a series on migrating SQL Server to PostgreSQL. The remaining parts:

So the ported code lands in the right target style: