Skip site navigation (1) Skip section navigation (2)

Re: proposal for CallableStatements to handle multiple out

From: Kris Jurka <books(at)ejurka(dot)com>
To: Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: proposal for CallableStatements to handle multiple out
Date: 2004-06-18 18:49:16
Message-ID: Pine.BSO.4.56.0406181337260.10504@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

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

In response to

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2004-06-18 21:47:01
Subject: Re: Prepare Statement
Previous:From: Kris JurkaDate: 2004-06-18 18:35:00
Subject: Re: Pg 7.4.2, jdbc 7.4, data type inet

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group