Re: java.sql.DatabaseMetaData.getProcedures and overloaded functions

On Thu, 2010-12-30 at 22:24 -0500, Kris Jurka wrote:
>
> On Thu, 30 Dec 2010, Thor Michael St?re wrote:
>
> > Is my understanding correct in that JDBC doesn't actually provide a
> > proper way of inspecting overloaded stored procedures, and so to get
> > that information reliably you should query the PostgreSQL system
> > catalogs? At least when I try to use the java.sql.DatabaseMetaData
> > getProcedures and getProcedureColumns methods on overloaded procedures I
> > see them repeated without any explicit distinction between them, except
> > of course that getProcedureColumns gives different datatypes for each
> > procedure.
> >
>
> Hmm, the JDBC4 spec has added an additional column to the getProcedures
> return value called SPECIFIC_NAME which might be for differentiating
> between overloaded functions.

Right, it says it should "uniquely" identify the function within the
schema, which as far as I can tell means that for overloaded functions
it would have to list up the input argument types. (Or at least the
other only unique way to identify a function I can think of is the OID,
which I can't believe one would want there.)
Given:

CREATE FUNCTION myfunction(in integer,in varchar, out timestamp)
RETURNS ...

It could perhaps return "myfunction(integer,varchar)". That would
incidentally be something that identified a specific function for an
ALTER/DROP FUNCTION command.

> It's not clear whether the function is
> supposed to be callable by that specific name or not.  It's also awkard to
> use because you can't pass the SPECIFIC_NAME from getProcedures to
> getProcedureColumns, so you'd have to do your own filtering of that
> ResultSet.

True. In my case however I just need to find all the functions that
matches a given name and their respective parameter types, so come to
think of it getProcedureColumns alone would be sufficient if it gave a
proper result for SPECIFIC_NAME that I could use just to map it on.

Thanks,
Thor Michael Støre