> There's no notion if the called object is a procedure or function. > Note: PostgreSQL can have a function that `returns void`, and it is hard to > tell if {call test()} refers to a function or procedure. > > Can functions and procedures be unified at the backend level? > For instance, support "CALL" for both of them. > Or support "select * ..." for both of them.
But there's a reason why CALL exists in the first place. It's not a synonym of SELECT and not supposed to do the same thing. In a SELECT or in a DML query in general you must be able to determine the structure of the resultset without executing the query.
In a CALL you're not supposed to be able to do that,
I disagree: "In a CALL you're are not required to determine the resultset structure prior to execution"
because: 1. A stored procedure should be able to return multiple resultsets with different structures.
But it may only return one
2. A stored procedure can decide dynamically of the structure of the resultset(s) it returns, and the caller will discover it as they're returned, not before.
The function itself doesn't care - this concern is about SELECT vs CALL invocation only, not the script definition.
Procedures should be able to do #1 or #2, but they cannot be called within DML queries.
Immaterial, we are talking about a straight "CALL proc()" statement here.
I fear like what is being asked here, to blur the distinction between functions and procedures in terms how the client-side workflow expects and handle results, would mean that we're going to loose the ability to do #1 and #2 in the future.
I don't see how allowing a function to be used as the object of CALL, but enforcing the existing CALL dynamics, will lead to that situation.
What I don't know is whether the limitations that are being imposed for CALL will break JDBC if existing SELECT statements are changed to CALL statements. Since JDBC has to be able to deal with CALL statements manually issued anyway it should be fairly straight forward for someone knowledgeable with the JDBC codebase to make that determination.
So, while its not really incumbent upon PostgreSQL to compensate for the decisions made by the JDBC driver PostgreSQL does bear some responsibility for the current situation due to its long period of non-implementation of the SQL Standard CALL (and stored procedure) feature. Loosening up the blanket restriction on functions not being a valid target of a CALL seems like something that should be strongly considered. Runtime failures for unsupported situations can still be thrown but to the extent that functions are effectively a subset of stored procedures it seems like most uses of a function as a target should be fully compatible with CALL semantics.