On Mon, Jul 23, 2018 at 12:07 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.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.
