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

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();"

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