Re: Procedure support improvements

Hmmm who knew you couldn't call a procedure inside a transaction. That just
seems broken

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On Sun, 21 Jul 2019 at 13:31, David Rader <david(dot)rader(at)gmail(dot)com> wrote:

> Hello -
>
> Since Procedures were introduced in PG 11, the workaround to invoke them
> with JDBC is to send the native "CALL proc()" SQL and let it be treated as
> a SQL statement, not a specific stored routine invocation.
>
> 1) When using transaction control inside the stored proc, an exception is
> generated if autoCommit is false - see example code attached.
> Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
> invalid transaction termination
>
> 2) Output parameters are not mapped as parameters, and app code cannot use
> registerOutputParameter or getInt() style retrieval. Instead, outputs are
> left in the result set and app code must retrieve the result and pull,
> creating a big difference between how Procedures and Functions are invoked.
>
> I propose improving support for procedures. Either:
> (1) add support for "CALL proc()" to be treated as a routine invocation so
> that output parameters can be registered, no begin transaction is silently
> sent from driver, and calling a procedure and calling a function would be
> very similar (only differing in function still using the {call} escape
> syntax.
> or
> (2) change the {call} syntax to optionally support procedures. {? = call}
> would still be mapped to functions. Add a connection setting to control
> this change, and make default false, so that default stays backwards
> compatible with pre pg11 functionality.
>
> Thoughts?
>
>
>
>
>