Stored procedures

T-SQL procedures returning result sets are the hardest item in the whole project. The failure mode to avoid is a plausible-looking wrong function: a missing artifact is recoverable, one that compiles and returns the wrong rows is not.

When to use

Any CREATE PROCEDURE/FUNCTION, EXEC, CommandType.StoredProcedure, ParameterDirection.Output or ReturnValue, RAISERROR/THROW, BEGIN TRY, cursor, or sp_executesql.

Decision procedure

1. Classify the procedure's shape (PG-PROC-009) - see the table below.
2. Choose one of three outcomes per procedure, and record it:
   • port to PL/pgSQL, keeping the T-SQL version untouched;
   • lift the logic into application code with portable SQL - preferred when the procedure is thin;
   • flag as blocking.
3. Apply the naming rules (PG-PROC-011, PG-PROC-012) to every ported body. These are mandatory, not stylistic.
4. Convert the call site (PG-PROC-001) - the calling convention depends on what the body became, so the two decisions are one decision.
5. Anything that cannot be converted confidently gets a stub: the original T-SQL as a comment block, a -- TODO(dualdb: F-xxxx) header, and a MANUAL_REVIEW.md entry.

Shape table

T-SQL shape	PostgreSQL target	Tier
Single SELECT, no side effects	FUNCTION ... RETURNS SETOF <composite> or RETURNS TABLE(...)	auto if the columns are knowable from the SELECT, else review
DML only, no result set	PROCEDURE (PostgreSQL 11+)	auto
DML plus a SELECT result	FUNCTION returning a table, or split - changes call-site semantics	review
Multiple result sets	no direct equivalent - refcursors or split calls	manual (PG-PROC-014)
Output parameters	INOUT parameters or a composite return	review
RETURN <int> status	not available on functions - an out param or a column	review (PG-PROC-002)
Dynamic SQL via sp_executesql	EXECUTE ... USING	review (PG-DYN-003)
TRY/CATCH + RAISERROR/THROW	BEGIN ... EXCEPTION WHEN OTHERS + RAISE	review (PG-PROC-013)
Temp tables #t	temp tables work; no automatic recompile semantics	auto with a note (PG-TEMP-001)
Table variables @t	temp table or CTE - different transaction visibility	review (PG-TEMP-003)
MERGE	INSERT ... ON CONFLICT - not semantically identical	review (PG-FUNC-010)
CROSS/OUTER APPLY	LATERAL	auto (PG-FUNC-014)
SELECT ... FOR XML	no equivalent	manual
CLR integration, linked servers	no equivalent	manual, blocking

Rule table

<!-- dualdb:rules:begin categories=procedures,ddl --> <!-- generated from knowledge/rules/*.yaml by `dualdb rules sync` - do not edit -->

id	SQL Server	PostgreSQL	tier	note
PG-DYN-002	BEGIN TRAN; ALTER TABLE dbo.[Order] ADD Total money; ... COMMIT;	BEGIN; ALTER TABLE dbo."Order" ADD "Total" numeric(19,4); ... COMMIT;	auto	PostgreSQL has fully transactional DDL
PG-PROC-001	EXEC dbo.usp_ArchiveOrders @days = 30	CALL dbo.usp_archive_orders(par_days => 30)	review	EXEC usp_x @a -> SELECT fn_x(@a) or CALL proc_x(@a)
PG-PROC-002	RETURN 1 -- not found	par_status := 1; -- INOUT parameter	review	A procedure RETURN code has no PostgreSQL analogue
PG-PROC-003	[Id] INT IDENTITY(1,1) NOT NULL	"Id" integer GENERATED BY DEFAULT AS IDENTITY NOT NULL	auto	IDENTITY(1,1) -> GENERATED BY DEFAULT AS IDENTITY
PG-PROC-004	SET IDENTITY_INSERT dbo.[Order] ON; INSERT INTO dbo.[Order] (Id, Name) VALUES (1, 'a');...	INSERT INTO dbo."Order" ("Id", "Name") VALUES (1, 'a')	auto	SET IDENTITY_INSERT -> OVERRIDING SYSTEM VALUE
PG-PROC-005	IF OBJECT_ID('dbo.Order') IS NULL CREATE TABLE dbo.[Order] (Id int)	CREATE TABLE IF NOT EXISTS dbo."Order" ("Id" integer)	auto	IF OBJECT_ID(...) IS NULL -> CREATE ... IF NOT EXISTS
PG-PROC-006	[Total] AS (Quantity * UnitPrice) PERSISTED	"Total" numeric(19,4) GENERATED ALWAYS AS ("Quantity" * "UnitPrice") STORED	review	Computed column AS (expr) PERSISTED -> GENERATED ALWAYS AS (expr) STORED
PG-PROC-007	CREATE INDEX IX_Order_Active ON dbo.[Order] (CustomerId) WHERE IsActive = 1	CREATE INDEX "IX_Order_Active" ON dbo."Order" ("CustomerId") WHERE "IsActive"	auto	Filtered indexes and INCLUDE columns
PG-PROC-008	SELECT name FROM sys.tables WHERE name = 'Order'	SELECT tablename FROM pg_catalog.pg_tables WHERE tablename = 'Order'	review	Catalog queries against sys.* have no equivalent
PG-PROC-009	CREATE PROCEDURE dbo.usp_GetOrders @customerId INT AS SELECT * FROM dbo.[Order] WHERE C...	CREATE FUNCTION dbo.usp_get_orders(par_customer_id int) RETURNS SETOF dbo."Order" LANGU...	review	Choose a target shape for every T-SQL procedure
PG-PROC-010	no equivalent step on SQL Server after a data load	SELECT setval(pg_get_serial_sequence('dbo."Order"', 'Id'), (SELECT MAX("Id") FROM dbo."...	review	Re-sync sequences after a data migration
PG-PROC-011	CREATE PROCEDURE dbo.usp_Deactivate @id INT AS UPDATE dbo.[Order] SET IsActive = 0 WHER...	CREATE PROCEDURE dbo.usp_deactivate(par_id int) LANGUAGE plpgsql AS $$ BEGIN UPDATE dbo...	auto	PL/pgSQL parameters and locals need par_ and var_ prefixes
PG-PROC-012	SELECT COUNT(*) INTO var_count FROM dbo."Order" WHERE "CustomerId" = par_customer_id;	SELECT COUNT(*) INTO var_count FROM dbo."Order" o WHERE o."CustomerId" = par_customer_id;	auto	Qualify column references inside a function body
PG-PROC-013	BEGIN TRY INSERT ... END TRY BEGIN CATCH RAISERROR('failed', 16, 1) END CATCH	BEGIN INSERT ...; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'failed'; END	review	TRY/CATCH and RAISERROR -> EXCEPTION and RAISE
PG-PROC-014	CREATE PROCEDURE dbo.usp_GetOrderAndLines @id INT AS SELECT ... ; SELECT ... ;	no direct equivalent	manual	Multiple result sets from one procedure

<!-- dualdb:rules:end -->

The naming rules are mandatory (PG-PROC-011, PG-PROC-012)

PL/pgSQL resolves unqualified names against columns first. A parameter named id in a function touching a table with an id column either raises column reference "id" is ambiguous or - much worse - silently binds the column:

-- catastrophic: par_id named `id` would make this WHERE clause always true
DELETE FROM dbo."Order" WHERE "Id" = id;

So, in every ported body:

• parameters are prefixed par_ (plpgsql_param_prefix)
• locals are prefixed var_ (plpgsql_var_prefix)
• no parameter or variable reuses a column name
• every table gets an alias and every column reference is qualified with it

The T-SQL side keeps @id. .NET parameters keep @id. The prefixes do not propagate outwards - they exist only inside PL/pgSQL bodies.

Worked examples

1. add-pg-file - a read-only procedure

CREATE PROCEDURE dbo.usp_GetOrders @customerId INT AS
    SELECT Id, CustomerName FROM dbo.[Order] WHERE CustomerId = @customerId;

Single SELECT, no side effects, columns knowable:

CREATE FUNCTION dbo.usp_get_orders(par_customer_id int)
RETURNS TABLE ("Id" integer, "CustomerName" varchar) LANGUAGE sql AS $$
    SELECT o."Id", o."CustomerName"
      FROM dbo."Order" o
     WHERE o."CustomerId" = par_customer_id;
$$;

Call site: SELECT * FROM dbo.usp_get_orders(@customerId) instead of EXEC dbo.usp_GetOrders @customerId.

2. add-pg-file - DML with a row count

CREATE PROCEDURE dbo.usp_ArchiveOrders @days INT AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cutoff DATETIME2(7) = DATEADD(day, -@days, SYSUTCDATETIME());
    UPDATE o SET o.IsActive = 0
      FROM dbo.[Order] AS o JOIN dbo.Customer AS c ON c.Id = o.CustomerId
     WHERE o.CreatedUtc < @cutoff AND c.IsClosed = 1;
    SELECT @@ROWCOUNT AS Archived;
END

Rules: PG-FUNC-015 (SET NOCOUNT dropped), PG-DATE-004 (DATEADD), PG-DATE-002 (SYSUTCDATETIME), PG-FUNC-012 (UPDATE ... FROM reshaped), PG-TYPE-005 (IsClosed = 1), PG-FUNC-011 (@@ROWCOUNT), PG-PROC-011.

CREATE FUNCTION dbo.usp_archive_orders(par_days int)
RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
    var_cutoff timestamptz;
    var_count  integer;
BEGIN
    var_cutoff := (NOW() AT TIME ZONE 'UTC') + make_interval(days => -par_days);
    UPDATE dbo."Order" o SET "IsActive" = FALSE
      FROM dbo."Customer" c
     WHERE c."Id" = o."CustomerId"
       AND o."CreatedUtc" < var_cutoff
       AND c."IsClosed";
    GET DIAGNOSTICS var_count = ROW_COUNT;
    RETURN var_count;
END $$;

Note the UPDATE ... FROM reshaping: the target is not repeated in FROM. Repeating it would create a self-join and update the wrong rows - valid SQL, wrong answer.

3. manual-review - two result sets

CREATE PROCEDURE dbo.usp_GetOrderAndLines @id INT AS
    SELECT * FROM dbo.[Order] WHERE Id = @id;
    SELECT * FROM dbo.OrderLine WHERE OrderId = @id;

Trivial-looking and unconvertible (PG-PROC-014). PostgreSQL has no way to emit two result sets from one call that a client walks with NextResult. Three options, all changing the caller:

• Refcursors - preserves the single-call shape; the caller changes completely, the cursors live only inside the transaction, and Npgsql's handling is not equivalent to reading a second result set.
• Split into two calls - much simpler both sides, easy to test; extra round trip, and the atomic snapshot is lost unless the caller opens a transaction.
• One composite result - a JSON document or a discriminated row set; one round trip, but the application now does assembly the database used to do.

Output: a stub file with the original T-SQL as a comment, a -- TODO(dualdb: F-xxxx) header, and the entry in MANUAL_REVIEW.md. No generated function.

Traps

• A parameter named after a column is the worst bug in this area. It does not error; it deletes every row. Hence PG-PROC-011.
• UPDATE ... FROM reshaping is silent when wrong.
• A PL/pgSQL exception handler is an implicit subtransaction - the statement's effects roll back, unlike a T-SQL CATCH that often continues (PG-PROC-013).
• Table variables are not rolled back; temp tables are (PG-TEMP-003). A procedure that accumulated an error log in a @table loses it.
• CommandType.StoredProcedure behaves differently on Npgsql and must be verified per call site, not assumed (PG-PROC-001).
• RETURN <int> has no analogue and Npgsql has no ReturnValue parameter direction (PG-PROC-002).

Escalate when

• CLR integration, linked servers, FOR XML, or MERGE with OUTPUT.
• Dynamic sp_executesql whose statement cannot be reconstructed (PG-DYN-004).
• Cursors using SQL Server-specific options (FAST_FORWARD, SCROLL_LOCKS).
• Multiple result sets (PG-PROC-014).
• The procedure's result columns cannot be determined from its text - a SELECT * over a table whose schema the run does not have.

Do not

• Do not emit a plausible-looking wrong function. Emit a stub.
• Do not mutate the existing SQL Server script. The PostgreSQL artifact is a new file in a parallel tree.
• Do not propagate par_/var_ into T-SQL or into .NET parameter names.
• Do not "simplify" a procedure while porting it - no merged statements, no removed branches, no changed transaction boundaries.