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

Stored Procedures vs Functions in PostgreSQL

On this page
  1. Function or procedure: mapping SQL Server routines
  2. Converting scalar functions
  3. A stored procedure that returns rows becomes a function
  4. OUTPUT parameters become OUT parameters
  5. Procedures, for transaction control
  6. Check yourself

Function or procedure: mapping SQL Server routines

SQL Server has three kinds of routine: stored procedures, scalar functions and table-valued functions. PostgreSQL has two: functions and procedures. The mapping isn't one to one, and the most common migration mistake is turning every stored procedure into a PostgreSQL procedure.

Ask what the routine does, not what it's called:

The SQL Server routine...Becomes in PostgreSQLCalled with
Returns one value (scalar function)Function RETURNS numeric etc.SELECT f(1)
Returns rows (table-valued function)Function RETURNS TABLE (...)SELECT * FROM f(1)
Is a stored procedure that returns a result setFunction RETURNS TABLE (...)SELECT * FROM f(1)
Is a stored procedure with OUTPUT parametersFunction with OUT parametersSELECT * FROM f(1)
Commits or rolls back partway throughProcedureCALL p(1)
Just performs changes, no resultEither. A procedure reads more naturally.CALL p(1)

The reason is a single rule. A PostgreSQL function always runs inside the caller's transaction and can't COMMIT, but it can return rows and be used in a query. A PostgreSQL procedure (version 11+) can COMMIT and ROLLBACK inside its body, but it can't be used in a SELECT, and it doesn't return result sets the way a SQL Server procedure does. So most stored procedures, which exist to return data, become functions.

Converting scalar functions

SQL
-- T-SQL
CREATE FUNCTION dbo.GetOrderTotal (@OrderId INT) RETURNS DECIMAL(12,2) AS
BEGIN
    DECLARE @Total DECIMAL(12,2);
    SELECT @Total = SUM(Quantity * UnitPrice) FROM OrderItems WHERE OrderId = @OrderId;
    RETURN ISNULL(@Total, 0);
END;

When the body is a single query, a PostgreSQL function in plain LANGUAGE sql is the simplest translation, and the planner can often inline it into the calling query:

SQL
CREATE TABLE order_items (order_id int, quantity int, unit_price numeric(10,2));
INSERT INTO order_items VALUES (1, 2, 10.00), (1, 1, 5.50);

CREATE FUNCTION get_order_total(p_order_id int)
RETURNS numeric
LANGUAGE sql STABLE
AS $$
  SELECT coalesce(sum(quantity * unit_price), 0)
  FROM order_items
  WHERE order_id = p_order_id;
$$;

SELECT get_order_total(1);   -- 25.50

Use LANGUAGE plpgsql when you need variables, IF or exception handling (Day 8). STABLE tells the planner the function reads data but doesn't change it. Labeling functions correctly lets the planner optimize calls, and the PostgreSQL course explains the labels in detail.

A stored procedure that returns rows becomes a function

Here's the most common case: a procedure with parameters, a default and a result set, plus an OUTPUT row count.

SQL
-- T-SQL
CREATE PROCEDURE dbo.usp_GetCustomerOrders
    @CustomerId INT, @Status VARCHAR(20) = 'OPEN', @RowCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderDate, OrderTotal, Status
    FROM Orders WHERE CustomerId = @CustomerId AND Status = @Status
    ORDER BY OrderDate DESC;
    SET @RowCount = @@ROWCOUNT;
END;

In PostgreSQL it becomes a function with RETURNS TABLE:

SQL
CREATE TABLE orders (order_id int, customer_id int, order_date date, order_total numeric, status varchar(20));
INSERT INTO orders VALUES (1, 101, '2026-09-01', 120, 'OPEN'), (2, 101, '2026-09-15', 80, 'OPEN');

CREATE FUNCTION get_customer_orders(p_customer_id int, p_status varchar DEFAULT 'OPEN')
RETURNS TABLE (order_id int, order_date date, order_total numeric, status varchar)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
  RETURN QUERY
  SELECT o.order_id, o.order_date, o.order_total, o.status
  FROM orders o
  WHERE o.customer_id = p_customer_id AND o.status = p_status
  ORDER BY o.order_date DESC;
END;
$$;

SELECT * FROM get_customer_orders(101);

What changed:

  • Parameters lose the @ and gain a p_ prefix, which avoids clashing with column names. Defaults use DEFAULT 'OPEN'.
  • The result set is declared up front in RETURNS TABLE and produced by RETURN QUERY.
  • The column references are qualified (o.order_id). RETURNS TABLE columns are also variables inside the function, so an unqualified order_id would be ambiguous.
  • SET NOCOUNT ON disappears. PostgreSQL doesn't send "rows affected" messages that need suppressing.
  • @RowCount disappears too. The caller sees how many rows came back. Inside PL/pgSQL, GET DIAGNOSTICS v_count = ROW_COUNT is the equivalent of @@ROWCOUNT.

A bonus: because it's a function, callers can filter, join and aggregate the result, as in SELECT count(*) FROM get_customer_orders(101). That's impossible with EXEC.

Procedures that return several result sets have no direct equivalent. Split them into several functions, or return refcursors if the application can handle them. Splitting is usually cleaner.

OUTPUT parameters become OUT parameters

SQL
CREATE FUNCTION get_customer_stats(p_customer_id int, OUT total_orders bigint, OUT total_spent numeric)
LANGUAGE sql STABLE
AS $$
  SELECT count(*), coalesce(sum(order_total), 0)
  FROM orders WHERE customer_id = p_customer_id;
$$;

SELECT * FROM get_customer_stats(101);   -- 2 | 200

OUT parameters become the columns of the result row. The caller doesn't need the DECLARE @x; EXEC ... @x OUTPUT; SELECT @x dance.

Procedures, for transaction control

Use CREATE PROCEDURE when the routine must commit partway through, typically batch jobs that should keep their progress if they fail halfway:

SQL
CREATE TABLE orders_archive (LIKE orders);

CREATE PROCEDURE archive_orders_before(p_cutoff date, p_batch int DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
  v_moved int;
BEGIN
  LOOP
    WITH moved AS (
      DELETE FROM orders
      WHERE ctid IN (SELECT ctid FROM orders WHERE order_date < p_cutoff LIMIT p_batch)
      RETURNING *
    )
    INSERT INTO orders_archive SELECT * FROM moved;
    GET DIAGNOSTICS v_moved = ROW_COUNT;
    COMMIT;
    EXIT WHEN v_moved < p_batch;
  END LOOP;
END;
$$;

CALL archive_orders_before('2026-09-10');

The DELETE ... RETURNING feeds the INSERT in a single statement, so each batch moves rows atomically. One restriction: the COMMIT only works when the procedure is called directly, not from inside a function or an explicit transaction block that already has other work in it.

In DataGrip, functions and procedures appear under the schema's Routines node. Double-click one to see its full definition.

Check yourself

A function with RETURNS TABLE (...) and RETURN QUERY, called with SELECT * FROM list_invoices(...). PostgreSQL procedures don't return result sets like SQL Server procedures.

A procedure, called with CALL. A function always runs inside the caller's transaction and can't commit, so a function-based purge would delete everything in one huge transaction, holding locks and bloating WAL. A procedure can COMMIT after each batch, just like the T-SQL job.

The RETURNS TABLE column order_id is also a variable in the function, so an unqualified order_id in the query could mean either one. Qualify the column with its table alias.

Declare it as an OUT parameter. In a function, OUT parameters become the returned columns, which the caller reads with SELECT * FROM fn(...). In a procedure, OUT parameters (supported since PostgreSQL 14) are returned to the caller of CALL.

Volatility tells the planner whether it can cache or reorder calls. An IMMUTABLE function with the same inputs always returns the same output, so it can be used in indexes and evaluated once. Marking too strictly is a bug, though: a function that reads a table must not be IMMUTABLE.