Thread: Why CALL/PERFORM not part of core SQL?

Why CALL/PERFORM not part of core SQL?

From
"Guy Rouillier"
Date:
Short version of story: I'm converting some Java->Oracle code to PG.  It
uses the standard JDBC batch facility, which is simply a collection of
statements sent to the server as a group.  Because batches are executed
as a group, the individual statements in them are forbidden from
returning values.

The application is using batches of CALL statements to stored
procedures, which works fine with Oracle, since stored procs there do
not return values.  The closest approximation in PG is to use SELECT on
stored functions.  You can see where this is going: SELECT returns a
value (a JDBC ResultSet), so the code is bombing out with error
"org.postgresql.util.PSQLException: A result was returned when none was
expected."  The really embarrassing thing is that I discovered this same
problem 6 months ago and forgot about it; searching the JDBC list I
found my own posting!

Before I go back on JDBC to continue this discussion, I wanted to see if
there is a specific reason why CALL or PERFORM is not made part of the
core PG SQL implementation, as opposed to only being defined in
pl/pgsql.  I would think it might come in handy to other pl's.  The
alternative for Java, I suppose, is to allow these verbs and then
translate them to SELECT in the driver and throw away any return value.
That seems like more of a hack than a solution.  I suppose the same
could be said with respect to doing the same thing in the core language.

--
Guy Rouillier


Re: Why CALL/PERFORM not part of core SQL?

From
Jaime Casanova
Date:
On 11/18/05, Guy Rouillier <guyr@masergy.com> wrote:
> Short version of story: I'm converting some Java->Oracle code to PG.  It
> uses the standard JDBC batch facility, which is simply a collection of
> statements sent to the server as a group.  Because batches are executed
> as a group, the individual statements in them are forbidden from
> returning values.
>
> The application is using batches of CALL statements to stored
> procedures, which works fine with Oracle, since stored procs there do
> not return values.  The closest approximation in PG is to use SELECT on
> stored functions.  You can see where this is going: SELECT returns a
> value (a JDBC ResultSet), so the code is bombing out with error
> "org.postgresql.util.PSQLException: A result was returned when none was
> expected."  The really embarrassing thing is that I discovered this same
> problem 6 months ago and forgot about it; searching the JDBC list I
> found my own posting!
>
> Before I go back on JDBC to continue this discussion, I wanted to see if
> there is a specific reason why CALL or PERFORM is not made part of the
> core PG SQL implementation, as opposed to only being defined in
> pl/pgsql.  I would think it might come in handy to other pl's.  The
> alternative for Java, I suppose, is to allow these verbs and then
> translate them to SELECT in the driver and throw away any return value.
> That seems like more of a hack than a solution.  I suppose the same
> could be said with respect to doing the same thing in the core language.
>
> --
> Guy Rouillier
>
>

declare your functions as RETURNING void

CREATE FUNCTION yourfunction RETURNS void AS $$...


and do "select yourfunction();"

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Re: Why CALL/PERFORM not part of core SQL?

From
"Guy Rouillier"
Date:
Jaime Casanova wrote:
> On 11/18/05, Guy Rouillier <guyr@masergy.com> wrote:
>> Short version of story: I'm converting some Java->Oracle code to PG.
>> It uses the standard JDBC batch facility, which is simply a
>> collection of statements sent to the server as a group.  Because
>> batches are executed as a group, the individual statements in them
>> are forbidden from returning values.
>>
>> The application is using batches of CALL statements to stored
>> procedures, which works fine with Oracle, since stored procs there do
>> not return values.  The closest approximation in PG is to use SELECT
>> on stored functions.  You can see where this is going: SELECT returns
>> a value (a JDBC ResultSet), so the code is bombing out with error
>> "org.postgresql.util.PSQLException: A result was returned when none
>> was expected."  The really embarrassing thing is that I discovered
>> this same problem 6 months ago and forgot about it; searching the
>> JDBC list I found my own posting!
>>
>> Before I go back on JDBC to continue this discussion, I wanted to see
>> if there is a specific reason why CALL or PERFORM is not made part of
>> the core PG SQL implementation, as opposed to only being defined in
>> pl/pgsql.  I would think it might come in handy to other pl's.  The
>> alternative for Java, I suppose, is to allow these verbs and then
>> translate them to SELECT in the driver and throw away any return
>> value. That seems like more of a hack than a solution.  I suppose the
>> same could be said with respect to doing the same thing in the core
>> language.
>>
>> --
>> Guy Rouillier
>>
>>
>
> declare your functions as RETURNING void
>
> CREATE FUNCTION yourfunction RETURNS void AS $$...
>
>
> and do "select yourfunction();"

Thanks, but I'm already doing that.  Because the SQL statement is a
SELECT, it's still generating a ResultSet.  The ResultSet happens to be
empty, but that is immaterial.

--
Guy Rouillier