Re: Callable Statements

floess(at)mindspring(dot)com writes:

> Nic:
>
> Here is a cheesy example question:
>
> Assume I had a function, foo_function, that returns SETOF foo_table.
> Internally, the function does nothing more than a "select * from
> foo" (also assume it does the return next, etc - again this is a
> cheesy question), are you saying it will be possible to use a
> CallableStatement and get a ResultSet?

I don't know. I haven't done anything about SETOF.

What you CAN do is return a ref cursor. Here's an example proc:

-- create or replace function list ( ) returns refcursor as '
declare
entrys refcursor;
begin
open entrys for
select id, title, date, entry from someentrys;
return entrys;
end;
-- ' language 'plpgsql';

> If so, can I assume that the CallableStatement will outperform using
> a PreparedStatement and calling the function as I've mentioned in my
> original post?

The performance characteristics of statements have been altered as
well. It's possible to turn off the downloading of the entire query.

In general procs will be quicker than PS's but only because they
generally live for longer.

Nic