Re: [HACKERS] Where are we on stored procedures?

Gavin Sherry <swm@linuxworld.com.au> writes:
> On Thu, 24 Feb 2005, Tom Lane wrote:
>> For instance, a procedure foo(x IN int, y OUT text, z OUT float)
>> could perhaps be called via
>> SELECT y, z FROM foo(42);
>> where foo(x) is seen as returning the rowtype (y text, z float).

> The composite type stuff is precisely what the JDBC driver does at the
> moment. I guess the feeling is that for those used to SPs in other
> databases, having to use composite types is a bit of a hack.

Yeah, but only because you have to do it explicitly.  I was wondering
whether we couldn't bury that mechanism under the hood.  (In particular,
given the improved support in 8.0 for anonymous record types, we could
in theory have the backend invent a record type on-the-fly to match
whatever list of OUT parameters a particular function has.)

>> (Gavin and Neil's first proposal also involved inventing a concept of
>> variables at the SQL level, primarily so that there would be something
>> to receive the results of OUT parameters.  I found that distasteful and
>> would prefer to avoid it.

> I'd like to hear what you had in mind for OUT parameters.

Essentially I'm thinking about the JDBC solution, but automated a bit
better.  You do
    SELECT * FROM function(a,b,c);
where only the IN (including INOUT) parameters are listed in the call,
and the OUT (including INOUT) parameters make up a result record type
that doesn't need to be explicitly declared.  I don't know yet what's
the cleanest way to handle this in terms of what appears in pg_proc.

If you insist we can allow "SELECT * FROM" to be spelled "CALL" or
some such, but I think I'd prefer to keep that notation in reserve
for "real" stored procedures.

This doesn't address the question of SETOF results, of course.  I'm
leaning towards returning those as cursors.

> This is one of the real tough issues Neil and I were trying to work out. I
> seem to remember you noting that without transaction control, SPs were
> irrelevant :-).

Well, I think that when people ask us for "stored procedures", most of
them mean that they want transaction control.  But the JDBC issues that
my Red Hat compatriots are currently worried about just have to do with
OUT parameters in a CallableStatement, so I'd like to make sure we solve
that in 8.1 regardless of whether anyone makes any progress on
outside-of-transactions stored procedures.

> As you've seen internally at Red Hat, the OUT parameter stuff is a real
> show stopper for the JDBC guys. It would be good to see this in 8.1 but
> I'm not sure when either Neil or I will find some time to look at it.

Rats, I was hoping you'd answer you had it about done ;-).  But if you
can pass over what you have, I'd like to see about pressing forward.

            regards, tom lane