Published: 2026-06-14

PostgreSQL has stored procedures and functions, but a T-SQL stored procedure rarely drops in unchanged. This is a worked conversion — one realistic procedure, line by line — plus the checklist of transforms that covers most of the others.

First, the honest framing: there is no reliable automatic T-SQL → PL/pgSQL translator, and Jam SQL Studio doesn't claim to be one. When you migrate a SQL Server database to PostgreSQL, Jam moves table shape and data mechanically and then hands you a by-name inventory of the procedures, functions, views, and triggers that still need a human. This post is how you clear that inventory.

The starting procedure

Here's a procedure that does enough to be representative: a parameter, a local variable, a conditional, a guarded insert, and a returned result set.

-- T-SQL (SQL Server)
CREATE PROCEDURE dbo.AddOrder
    @CustomerId INT,
    @Amount     DECIMAL(10,2),
    @NewOrderId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF @Amount <= 0
        THROW 50001, 'Amount must be positive', 1;

    INSERT INTO dbo.Orders (CustomerId, Amount, CreatedAt)
    VALUES (@CustomerId, @Amount, GETDATE());

    SET @NewOrderId = SCOPE_IDENTITY();

    SELECT Id, CustomerId, Amount, CreatedAt
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY CreatedAt DESC;
END;

That one procedure touches six things that change in PostgreSQL: the parameter sigil, SET NOCOUNT, THROW, GETDATE(), SCOPE_IDENTITY(), and the implicit result set. Watch each one.

The converted function

-- PL/pgSQL (PostgreSQL)
CREATE FUNCTION add_order(
    p_customer_id INT,
    p_amount      NUMERIC(10,2),
    OUT new_order_id INT
)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Amount must be positive';
    END IF;

    INSERT INTO orders (customer_id, amount, created_at)
    VALUES (p_customer_id, p_amount, now())
    RETURNING id INTO new_order_id;
END;
$$;

Going change by change:

• @CustomerId → p_customer_id. No sigil; a p_ prefix is the common convention to keep parameters from colliding with column names.
• SET NOCOUNT ON → deleted. PostgreSQL has no row-count chatter to suppress; the line simply disappears.
• THROW → RAISE EXCEPTION. PostgreSQL raises by message (and optional SQLSTATE), not by the numeric error id T-SQL uses.
• GETDATE() → now(). One of the most common silent breakers — GETDATE() doesn't exist in PostgreSQL.
• SCOPE_IDENTITY() → INSERT ... RETURNING id INTO new_order_id. This is the big one. PostgreSQL has no session-scoped "last identity"; you capture the generated key on the INSERT itself with RETURNING. It's actually safer — no chance of catching a trigger's insert by accident.
• @NewOrderId OUTPUT → OUT new_order_id INT. An OUT parameter; the function returns it as its result.

The result-set SELECT is the one judgement call. A T-SQL procedure can return both an OUTPUT parameter and a result set. A PL/pgSQL function can't do both cleanly, so you choose: keep the OUT value (above) and let the caller re-query, or drop the OUT and make the function RETURNS TABLE(...) that returns the recent orders. Pick based on what the caller actually used — splitting a do-everything procedure into two focused functions is usually the right move.

When you do want a result set

If the caller wanted the rows, not the id, convert to a table-returning function instead:

CREATE FUNCTION recent_orders(p_customer_id INT)
RETURNS TABLE (id INT, customer_id INT, amount NUMERIC(10,2), created_at TIMESTAMP)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT o.id, o.customer_id, o.amount, o.created_at
        FROM orders o
        WHERE o.customer_id = p_customer_id
        ORDER BY o.created_at DESC;
END;
$$;

Two gotchas worth internalising: qualify the columns (o.id) so they don't collide with the RETURNS TABLE output names, and remember the caller now does SELECT * FROM recent_orders(42) — a function is queried, not EXEC'd.

The transform checklist

Most procedures are some combination of these. Keep it next to your editor:

For the full function-name map (ISNULL → COALESCE, LEN → length, DATEADD → INTERVAL, and the rest), see the T-SQL → PostgreSQL function cheat sheet, and for the conceptual side-by-side the T-SQL vs PL/pgSQL reference.

How Jam SQL Studio makes the port manageable

The schema and data are the part you shouldn't do by hand, and Jam doesn't make you:

1. Migrate the tables first. Migrate SQL Server to PostgreSQL moves every table — types, identity, defaults, keys, indexes, and rows — so the procedures you port have real tables to run against.
2. Get the to-do list. Run a cross-engine Schema Compare between the SQL Server source and the PostgreSQL target. Every procedure and function that exists on one side and not the other shows up by name — that's your port checklist, and it shrinks as you go.
3. Port with both sides visible. Script the original procedure as CREATE in one tab, open a PostgreSQL query editor in another, and rewrite using the checklist above. Both connections live in the same window, so you're never alt-tabbing between two tools.

You still write the PL/pgSQL — that judgement is yours — but everything around it is mechanical, and Jam keeps the mechanical parts mechanical so your attention stays on the logic.

Frequently asked questions

Download Jam SQL Studio — free for personal use on macOS, Windows, and Linux. Cross-engine migration and Schema Compare are built in.