Vladimir Sitnikov wrote: > 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, because: 1. A stored procedure should be able to return multiple resultsets with different structures. 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. During the development of procedures, Peter Eisentraut posted an experimental patch [1] that demonstrated #1 and #2 and told he didn't have the time to pursue this direction, but hopefully that doesn't mean we'll never be able to do that in PG12 or after. Functions cannot do #1 or #2, but they can be called anywhere in a query. Procedures should be able to do #1 or #2, but they cannot be called within DML queries. 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. Personally I understood it as an important conceptual distinction between functions and procedures that the function exposes a contract to return a specific datatype that can be unambiguously determined at PREPARE time, whereas the procedure is specifically not bound by any such contract, with the consequence that CALL proc(..) cannot be prepared, or possibly that it cannot be run through prepare/bind/execute steps with the extended query protocol, unlike SELECT or more generally DML queries. Is that understanding of procedures vs functions incorrect? [1] https://www.postgresql.org/message-id/4580ff7b-d610-eaeb-e06f-4d686896b...@2ndquadrant.com Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite