Writing Portable Stored Procedures: PL/pgSQL, T-SQL, PL/SQL

1. Why stored procedures are barely portable

While declarative SQL such as SELECT and JOIN is at least partly standardized, that barely holds for stored procedures. The ANSI SQL standard does define a procedural extension with SQL/PSM (Persistent Stored Modules), but none of the major commercial systems adheres to it strictly. PostgreSQL uses PL/pgSQL, Microsoft SQL Server uses Transact-SQL (T-SQL), and Oracle uses PL/SQL, three languages that differ substantially in syntax, error handling, and language scope, even though they pursue similar concepts.

This lack of standardization in stored procedures is no accident, but a direct consequence of history: each system developed its procedural language at a time when no practical standard existed, and has since had to maintain backward compatibility with millions of lines of production code. Anyone writing business logic in stored procedures who later needs to switch databases faces one of the most expensive migration tasks in the entire database world. This article shows exactly where the three languages diverge and how to structure stored procedures so that a later migration remains realistic anyway.

2. Basic structure: CREATE PROCEDURE compared

Even the basic structure of a stored procedure differs noticeably between the three systems. PostgreSQL defines a function or procedure with CREATE FUNCTION or CREATE PROCEDURE, followed by a $$ quoted block containing the PL/pgSQL code and closed with LANGUAGE plpgsql. SQL Server uses CREATE PROCEDURE without dollar quoting, instead with the keyword AS before the code block and BEGIN...END as the block delimiter. Oracle structures stored procedures with CREATE OR REPLACE PROCEDURE using an explicit IS or AS section before declaring local variables and BEGIN...END for the execution block.

These structural differences in stored procedures seem cosmetic at first glance, but add up to considerable manual effort during a migration, since practically every single line needs syntax adjustments. An automated search and replace almost never works reliably here, because the three languages maintain different comment syntax, different termination characters, and different case conventions for keywords.

-- PostgreSQL: PL/pgSQL, dollar-quoted body
CREATE OR REPLACE PROCEDURE update_stock(p_product_id INT, p_quantity INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE products
    SET stock = stock - p_quantity
    WHERE id = p_product_id;
END;
$$;

-- SQL Server: T-SQL
CREATE PROCEDURE UpdateStock
    @ProductId INT,
    @Quantity INT
AS
BEGIN
    UPDATE Products
    SET Stock = Stock - @Quantity
    WHERE Id = @ProductId;
END;

-- Oracle: PL/SQL
CREATE OR REPLACE PROCEDURE update_stock(
    p_product_id IN NUMBER,
    p_quantity IN NUMBER
) IS
BEGIN
    UPDATE products
    SET stock = stock - p_quantity
    WHERE id = p_product_id;
END update_stock;
/

3. Variable declaration and assignment

The three procedural languages also go their own way with variables. PL/pgSQL declares variables in a separate DECLARE section before the BEGIN block, with the type after the variable name, similar to standard SQL column definitions. T-SQL uses the @ prefix for all variable names and declares them directly in the code with DECLARE @variable INT, without forcing a separate declaration block. PL/SQL follows a similar structure to PL/pgSQL with a declaration section before BEGIN, but consistently uses the assignment operator :=, while T-SQL allows both SET @var = value and SELECT @var = value.

These differences in stored procedures seem trivial but are a common source of bugs in manual porting, because developers habitually carry over the source language's syntax into the target language. A PL/pgSQL developer trying to write T-SQL easily forgets the @ prefix, while a T-SQL developer writing PL/pgSQL often forgets the assignment operator := instead of =, which produces a subtle, hard to find syntax error in PostgreSQL.

-- PostgreSQL: PL/pgSQL variable declaration
CREATE OR REPLACE FUNCTION calculate_discount(p_total NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_discount_rate NUMERIC := 0.1;
    v_discount_amount NUMERIC;
BEGIN
    v_discount_amount := p_total * v_discount_rate;
    RETURN v_discount_amount;
END;
$$;

-- SQL Server: T-SQL, @ prefix, no separate declaration block
CREATE FUNCTION CalculateDiscount(@Total DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @DiscountRate DECIMAL(4,2) = 0.1;
    DECLARE @DiscountAmount DECIMAL(10,2);
    SET @DiscountAmount = @Total * @DiscountRate;
    RETURN @DiscountAmount;
END;

4. Control flow: conditions and loops

Conditional branches look similar at first glance in all three languages but differ in detail. PL/pgSQL uses IF ... THEN ... ELSIF ... ELSE ... END IF, PL/SQL follows almost identical syntax with ELSIF instead of ELSE IF, while T-SQL uses the plainer IF ... BEGIN ... END ELSE BEGIN ... END without a dedicated keyword for ELSIF, which quickly becomes hard to read with many nested conditions.

Loops show the greatest variety: PL/pgSQL offers LOOP, WHILE, and FOR loops with EXIT WHEN for breaking out. PL/SQL has very similar constructs, supplemented by FORALL for high performance bulk operations, a feature neither PL/pgSQL nor T-SQL know in this form. T-SQL traditionally limits itself to WHILE loops, since classic FOR loops are missing, which encourages set based thinking but leads to more cumbersome code than in the other two languages for row by row processing.

-- PostgreSQL: PL/pgSQL FOR loop over a query result
CREATE OR REPLACE PROCEDURE archive_old_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id FROM orders WHERE created_at < NOW() - INTERVAL '1 year'
    LOOP
        INSERT INTO orders_archive SELECT * FROM orders WHERE id = r.id;
        DELETE FROM orders WHERE id = r.id;
    END LOOP;
END;
$$;

-- SQL Server: T-SQL WHILE loop with a cursor-based approach
CREATE PROCEDURE ArchiveOldOrders
AS
BEGIN
    DECLARE @OrderId INT;
    DECLARE order_cursor CURSOR FOR
        SELECT Id FROM Orders WHERE CreatedAt < DATEADD(year, -1, GETDATE());

    OPEN order_cursor;
    FETCH NEXT FROM order_cursor INTO @OrderId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO OrdersArchive SELECT * FROM Orders WHERE Id = @OrderId;
        DELETE FROM Orders WHERE Id = @OrderId;
        FETCH NEXT FROM order_cursor INTO @OrderId;
    END;

    CLOSE order_cursor;
    DEALLOCATE order_cursor;
END;

5. Error handling: EXCEPTION, TRY/CATCH, EXCEPTION block

Error handling is one of the areas where stored procedures diverge the most in philosophy between systems. PL/pgSQL uses an EXCEPTION block inside the BEGIN...END construct, which can catch specific error conditions such as WHEN unique_violation. T-SQL has followed a TRY...CATCH model since SQL Server 2005, structurally reminiscent of programming languages like C#, working with ERROR_MESSAGE(), ERROR_NUMBER(), and similar functions inside the CATCH block.

PL/SQL also uses an EXCEPTION block, but with predefined names such as NO_DATA_FOUND or TOO_MANY_ROWS for common error cases, supplemented by custom exceptions via RAISE_APPLICATION_ERROR. These three different error handling models cannot be mechanically translated into each other, because the available error codes, the granularity of error handling, and even the concept of nested transactions on error differ between systems.

-- PostgreSQL: EXCEPTION block within PL/pgSQL
CREATE OR REPLACE PROCEDURE safe_insert_customer(p_email TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO customers (email) VALUES (p_email);
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Customer with email % already exists', p_email;
END;
$$;

-- SQL Server: TRY/CATCH block in T-SQL
CREATE PROCEDURE SafeInsertCustomer @Email VARCHAR(255)
AS
BEGIN
    BEGIN TRY
        INSERT INTO Customers (Email) VALUES (@Email);
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE();
    END CATCH
END;

6. Processing cursors and result sets

All three languages support cursors for row by row processing of result sets, but the explicit handling differs. PL/pgSQL mostly encapsulates cursor logic implicitly via FOR record IN query LOOP, which makes the explicit OPEN/FETCH/CLOSE cycle unnecessary for most use cases. T-SQL traditionally requires the explicit cycle with DECLARE CURSOR, OPEN, FETCH NEXT inside a WHILE loop, and a final CLOSE and DEALLOCATE, producing considerably more boilerplate.

PL/SQL offers both variants: implicit cursors via FOR record IN (SELECT ...) LOOP, similar to PL/pgSQL, and explicit cursors with CURSOR cursor_name IS SELECT ... for cases where more control is needed, such as parameterized cursors or bulk fetch operations with BULK COLLECT. For migrations that means: T-SQL cursor code is usually the most expensive part of a port, because it can almost always be simplified to a much more compact implicit loop in PL/pgSQL, but that simplification requires manual analysis rather than automatic translation.

7. Return values and OUT parameters

Stored procedures also differ fundamentally in return values. PostgreSQL conceptually separates functions, which always return a value via RETURN, from procedures, which instead use OUT or INOUT parameters for return values, since PostgreSQL 11 also with a genuine CALL invocation instead of SELECT. T-SQL has OUTPUT parameters directly in the procedure signature and additionally an implicit integer return value via RETURN, traditionally used for status codes, not business data.

PL/SQL distinguishes between functions (always with a RETURN value) and procedures (with IN, OUT, and IN OUT parameters), similar to PostgreSQL, but additionally allows package based organization of multiple related procedures and functions in a named namespace, a concept neither PostgreSQL nor SQL Server know in this form. This package structure is one of the reasons large PL/SQL codebases are particularly expensive to break apart during a migration, since package boundaries rarely align cleanly with modular boundaries in other systems.

8. Strategies for migratable stored procedures

Given the substantial differences between PL/pgSQL, T-SQL, and PL/SQL, a deliberate architecture decision pays off before business logic is written into stored procedures. The most important principle: stored procedures should limit themselves to operations that genuinely benefit from proximity to the data, such as bulk updates, complex set based aggregations, or operations that save many round trips to the application. Pure business rules that would also work in the application layer are better kept there, since application code is generally considerably more portable between database systems than procedural SQL.

Where stored procedures are unavoidable, a consistent separation between simple, mechanically translatable constructs (variable declaration, simple conditions, standard SELECT/INSERT/UPDATE) and complex, language specific constructs (cursors, exception handling, package structures) helps. Anyone who deliberately keeps the complex parts small and well documented reduces migration effort considerably, even though full portability between PL/pgSQL, T-SQL, and PL/SQL will realistically never be achievable.

9. PL/pgSQL, T-SQL, and PL/SQL side by side

The following table summarizes the key structural differences between the three procedural languages.

This comparison shows why stored procedures almost always need to be rewritten rather than translated, even for a similar business task. Migration effort depends heavily on how many language specific features like package structures, FORALL bulk operations, or explicit cursors were actually used.

10. Summary

Stored procedures are one of the least portable layers of SQL, because PL/pgSQL, T-SQL, and PL/SQL differ substantially from each other in syntax, error handling, cursor processing, and namespace organization despite similar underlying concepts. The SQL/PSM standard exists on paper, but is not strictly implemented by any of the three systems, so any migration between these languages remains at its core a manual rewrite, not a mechanical translation.

Anyone who deliberately limits stored procedures to operations that genuinely benefit from database proximity, and avoids complex, language specific constructs such as package structures or explicit cursors where possible, reduces migration effort considerably. The most pragmatic strategy remains to keep business logic primarily in the application layer and use stored procedures deliberately for performance critical, data proximate operations, with clear documentation of which language features were used in each case.

11. FAQ: Writing Portable Stored Procedures