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

Jaime Casanova wrote:
> On 11/18/05, Guy Rouillier <guyr(at)masergy(dot)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