Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Something like this,

Everything must be done on call, due to polymorphic signatures, this
can be kept in short living cache, but bear in mind user can alter
procedure in meantime.

When JDBC driver will detect if procedure call statement is created.
1. Determine procedure oid - how? procedures may have not qualified
name. Is any function on backend that will deal with schema search path?
You may need to pass procedure parameters or at least types? or we need
to mirror backend code to Java?
2. Download procedure signature and parse, determine what is input and
what is output.
3. Determine how many output parameters user registered, if 1st
parameter is ? = exec(?, ?)
4. If only 1 parameter is output (and its UDT, pure UDT due to relkind)
use SELECT (RESULT) as "your_param_name" FROM f(params) AS RESULT, if I
remember well using () puts all in on record

Above will resolve some other problems in JDBC.

Ad 3. Problem is with 1st parameter, actually result of such procedure
may be record, so I think I should get in our address example, when call
? = ench(addres ?), result set like
address, address
But this is to discussion.

Postgresql has own roads, far away from support of any standard.

On Thu, 17 Feb 2011 13:14:46 +1300, Oliver Jowett wrote:
> On 17/02/11 04:23, Tom Lane wrote:
>> Florian Pflug <fgp(at)phlo(dot)org> writes:
>>> Hm, I've browsed through the code and it seems that the current
>>> behaviour
>>> was implemented on purpose.
>>
>> Yes, it's 100% intentional. The idea is to allow function authors
>> to
>> use OUT-parameter notation (in particular, the convention of
>> assigning
>> to a named variable to set the result) without forcing them into the
>> overhead of returning a record when all they want is to return a
>> scalar.
>> So a single OUT parameter is *supposed* to work just like a function
>> that does "returns whatever" without any OUT parameters.
>>
>> Even if you think this was a bad choice, which I don't, it's far too
>> late to change it.
>
> Any suggestions about how the JDBC driver can express the query to
> get
> the behavior that it wants? Specifically, the driver wants to call a
> particular function with N OUT or INOUT parameters (and maybe some
> other
> IN parameters too) and get a resultset with N columns back.
>
> The current approach is to say "SELECT * FROM f(params) AS RESULT"
> which
> works in all cases *except* for the case where there is exactly one
> OUT
> parameter and it has a record/UDT type.
>
> Oliver