Stored Procedures, NPGSQL, and returning a recordset

Question

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";
$$;

Note that there's no real need to use CommandType.StoredProcedure. You can easily invoke a stored procedure via CALL, and a stored function via SELECT * FROM. The same is typically the case in other databases. — Shay Rojansky
– Shay Rojansky, Commented Sep 27, 2023 at 10:49

Erwin Brandstetter · Accepted Answer · 2023-09-27 00:34:28Z

2

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:

How to return a value from a stored procedure (not function)?

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

How to get result set from PostgreSQL stored procedure?

answered Sep 27, 2023 at 0:34

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored Procedures, NPGSQL, and returning a recordset

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related