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

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 types for that function. This argument list name can then be use to retrieve the corresponding parameters for that specific version.

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