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

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

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: java.sql.DatabaseMetaData.getProcedures and overloaded functions
Date: 2010-12-31 15:04:20
Message-ID: ifkrdb$l2j$1@dough.gmane.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

pgsql-jdbc by date

Next:From: Tom LaneDate: 2010-12-31 16:09:21
Subject: Re: java.sql.DatabaseMetaData.getProcedures and overloaded functions
Previous:From: Thor Michael StøreDate: 2010-12-31 14:43:47
Subject: Re: java.sql.DatabaseMetaData.getProcedures and overloaded functions

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