Thread: java.sql.DatabaseMetaData.getProcedures and overloaded functions
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. Thanks, Thor Michael Støre
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. 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. http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getProcedures%28java.lang.String,%20java.lang.String,%20java.lang.String%29 Kris Jurka
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
Thor Michael Støre wrote on 30.12.2010 20:47:
> 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.
>
I had to deal with this as well, and I finally implemented my own Postgres specific versions of getProcedures() and
getProcedureColumns()in order to be able to support overloaded functions in my SQL Workbench.
What I essentially did, was to retrieve the procedures using my own SQL Statement that would also return all argument
typesfor that function. This argument list name can then be use to retrieve the corresponding parameters for that
specificversion.
So getting the procedures is something like this:
SELECT NULL AS PROCEDURE_CAT,
n.nspname AS PROCEDURE_SCHEM,
p.proname AS PROCEDURE_NAME,
d.description AS REMARKS,
array_to_string(p.proargtypes, ';') as PG_ARGUMENTS,
case when p.proisagg then 'aggregate' else 'function' end as proc_type
FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid)
LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc')
LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog')
WHERE p.pronamespace=n.oid
The PG_ARGUMENTS is the "magical" thing here.
The value of that column can (more or less) be used later when retrieving the columns:
SELECT format_type(p.prorettype, NULL) as formatted_type,
t.typname as pg_type,
coalesce(array_to_string(proallargtypes, ';'), array_to_string(proargtypes, ';')) as argtypes,
array_to_string(p.proargnames, ';') as argnames,
array_to_string(p.proargmodes, ';') as modes,
t.typtype
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
JOIN pg_catalog.pg_type t ON p.prorettype = t.oid
WHERE n.nspname = 'public'
AND p.proargtypes = cast('23 23' as oidvector);
Feel free to have a look at the source code (http://www.sql-workbench.net).
The class in question is workbench.db.postgres.PostgresProcedureReader.
Hope this helps.
Regards
Thomas
Thor Michael =?ISO-8859-1?Q?St=F8re?= <thormichael@gmail.com> writes:
> On Thu, 2010-12-30 at 22:24 -0500, Kris Jurka wrote:
>> 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.)
I'll bet a nickel that the expectation is for this to be pulled from
information_schema.routines.specific_name, which is defined thusly in
Postgres:
CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier)
... FROM pg_proc p
As best I can tell from the SQL spec, the <specific name> of a function
is implementation-dependent, so this is a compliant way of doing it.
regards, tom lane
On Fri, 2010-12-31 at 11:09 -0500, Tom Lane wrote: > Thor Michael =?ISO-8859-1?Q?St=F8re?= <thormichael@gmail.com> writes: > > 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.) > > I'll bet a nickel that the expectation is for this to be pulled from > information_schema.routines.specific_name, which is defined thusly in > Postgres: > > CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) > ... FROM pg_proc p > > As best I can tell from the SQL spec, the <specific name> of a function > is implementation-dependent, so this is a compliant way of doing it. Aah, wasn't aware of that, that makes sense. Also, I see SPECIFIC_NAME was added to JDBC 4, so I imagine that to correctly add it one would override getProcedures and getProcedureColumns in org.postgresql.jdbc4.AbstractJdbc4DatabaseMetaData and have them return the additional column(s). I took a look at the code and it seem straight forward enough for getProcedures, but getProcedureColumns has six other new columns as well and seems a bit hairier so I'm not sure if should throw myself over adding this. Thanks either way to everyone, I quite understand the state of things now. - thormick
On Sun, 2 Jan 2011, Thor Michael St?re wrote: > Also, I see SPECIFIC_NAME was added to JDBC 4, so I imagine that to > correctly add it one would override getProcedures and > getProcedureColumns in > org.postgresql.jdbc4.AbstractJdbc4DatabaseMetaData and have them return > the additional column(s). I took a look at the code and it seem straight > forward enough for getProcedures, but getProcedureColumns has six other > new columns as well and seems a bit hairier so I'm not sure if should > throw myself over adding this. > I've checked in a fix for this to CVS so that getProcedures and getProcedureColumns have all of the columns defined in JDBC4 including SPECIFIC_NAME. Kris Jurka
On Mon, 2011-01-03 at 14:23 -0500, Kris Jurka wrote: > > I've checked in a fix for this to CVS so that getProcedures and > getProcedureColumns have all of the columns defined in JDBC4 including > SPECIFIC_NAME. > I've checked it out, built it and have been playing around with it for a while now, and I seem to be able to get all the information I need from standard JDBC interfaces now. Thank you! - thormick