Search code examples
.netpostgresqlstored-proceduresnpgsqlrecordset

Stored Procedures, NPGSQL, and returning a recordset

Version 7.0 of the Npgsql dotnet client interface to PostgreSQL databases introduced a breaking change in which CommandType.StoredProcedure now calls a PostgreSQL PROCEDURE rather than a FUNCTION.

Our application uses generic code that works with several database vendors. Generic code uses objects like DbCommand and DbConnection and DbProviderFactories. Every database call in our vendor independent app is a call to a stored procedure. About 1/2 the stored procedures return results as a record set from a SELECT statement.

I know that the Npgsql client can be forced back to legacy behaviour where it calls a PostgreSQL FUNCTION instead of a PROCEDURE. See: Stored functions and procedures

But, from a go forward perspective, is there a way (what is the plan) for having a PostgreSQL PROCEDURE return a record set - consistent with other database vendors?

Is there some syntax variation on OUT parameters that can return a record set in a PROCEDURE?

Here is a sample PROCEDURE that DOES NOT return any data through Npgsql...

CREATE OR REPLACE PROCEDURE public."AttributeSelect"()
LANGUAGE 'sql'
AS $$
   SELECT "Id" FROM "Attribute";
$$;
Solution

  • No, there is currently (Postgres 16) no way to return a set from a PROCEDURE. You can return a single row with OUT or INOUT parameters. See:

    To return a set, use a FUNCTION. A so-called "set-returning function", a.k.a. "table-function". Related: