Re: proposal for CallableStatements to handle multiple out

On Fri, 18 Jun 2004, Dave Cramer wrote:

> I'm in the process of writing a patch to get the driver to handle
> multiple out parms.
>
> One mechanism is to create a composite type and a function such as:
>
> create type Numeric_Proc_RetType as(it1 numeric(30,15),it2
> numeric(30,15),it3 numeric(30,15));");
>
> create function Numeric_Proc(numeric(30,15),numeric(30,15),
> numeric(30,15)) returns Numeric_Proc_RetType as 'declare work_ret
> record; begin select * into work_ret from Numeric_Tab; return work_ret;
> end;' language 'plpgsql';
>

To expand on this the key part of the plan is that a CallableStatement
like "{ call Numeric_Proc(?,?,?)}" where all three arguments are
registered as output parameters would be transformed by the driver into:
SELECT f.it1, f.it2, f.it3 FROM (SELECT Numeric_Proc(1,2.2,3)) f;

The key is using the intermediate composite type to hold multiple outputs
so they can be extracted by the select.  The interface is reasonably clear
from a Java perspective, but rather awkward for a backend function writer.

Some of the implementaion issues involved are:

1) How can you determine the correct column names to use with the
transformed SELECT statement.  This will require some metadata lookup
which could be especially tricky given our ability to have overloaded
functions.

2) Are CallableStatements with only one output parameter supposed to
return a simple value or a composite type of only one column?  Does it
make a difference if called like "{ call f(?)}" or "{?= call f()}"?

3) Now that composite types have full backend support we need to make sure
this doesn't conflict with using them as input or output types in
CallableStatements.

Kris Jurka