Re: Stored procedures and out parameters

On Mon, Jul 23, 2018 at 12:07 PM, Vladimir Sitnikov <
sitnikov(dot)vladimir(at)gmail(dot)com> wrote:

> 2) Just a side note: `CALL my_proc()` is not suitable for functions. That
> looks weird.
> Is the client expected to lookup system catalogs in order to tell if
> `my_proc` is procedure or function and use either `call my_proc` or `select
> * from my_proc()`?
> Issuing `call my_function()` fails with 42883 ERROR: my_func(unknown) is
> not a procedure
>
> Note: JDBC defines two options to call a stored procedure:
> {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
> {call <procedure-name>[(<arg1>,<arg2>, ...)]}
> see https://docs.oracle.com/javase/9/docs/api/java/sql/
> CallableStatement.html
>
> 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.
>
>
​IMO JDBC will need to version branch the textual transform of {call} to
"CALL" in v11 and continue with the transform to SELECT in v10 and
earlier. Recommend adding an override property to force SELECT syntax in
v11​. This regardless of whether the server accepts functions as the
object of a CALL; though if it does the override will then likely be a
fail-safe device instead of a fundamental decision point for the developer.

I'm not familiar with the JDBC enough to posit whether adding a catalog
lookup is something that should be done; but I'd probably not go down that
path without first trying the version+override solution alone.

David J.