PostgreSQL 9 JDBC driver returns incorrect metadata for stored procedures

I am in a team that develops a business intelligence (reporting) tool. We report that many sources include Stored Procedures. We use the metadata provided by the JDBC driver to determine the input and output parameters of the stored procedure.

PostgreSQL 9 JDBC drivers seem to incorrectly return metadata for procedure parameters.

For example, my stored procedure looks like this:

CREATE FUNCTION person(personid int)
RETURNS TABLE(person int, name varchar(200)) AS $$
BEGIN
    RETURN QUERY SELECT ipperson, firstname FROM person
                 WHERE ipperson = personid;
END;
$$ LANGUAGE plpgsql;

So it has one parameter inside, two columns are returned in the result set.

The PostgreSQL driver reports that there are 3 IN parameters.

• personid (parameter)
• person (first column returned)
• name (second column is returned)

no metadata to distinguish between types.

I am doing this with:

SELECT * FROM person(?);

( , )

, , , :

SELECT * FROM person(5) where person = 5;

, , ( , ?), .

, , - .

PostgreSQL 8, :

• personid ()

.

/:

• PostgreSQL Server 9.11 ( 16)
• PostgreSQL "8" 8.0 JDBC3 SSL ( 313)
• PostgreSQL "9" 9.3 JDBC4 ( 1100)

, , :

DB:

CREATE TABLE testtable (
    id integer,
    name varchar
);

INSERT INTO testtable VALUES (1, 'Bob');

CREATE FUNCTION testproc(itemid int)
RETURNS TABLE(id int, name varchar(200)) AS $$
BEGIN
    RETURN QUERY SELECT ipperson, firstname FROM testtable
                 WHERE id = itemid;
END;
$$ LANGUAGE plpgsql;

Java:

package com.hof.unittest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class TestPostgres {

    public static void main(String args[]) {

        try {

            Class.forName("org.postgresql.Driver");

            Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testdb", "admin", "admin");

            ResultSet rs = conn.getMetaData().getProcedureColumns(null, null, "testproc", null);

            System.out.println("Driver: " + conn.getMetaData().getDriverVersion());

            while (rs.next()) {

                System.out.println("Parameter Name: " + rs.getString(4) + " Paramter Type: " + rs.getShort(5) + " Data Type: " + rs.getInt(6));

            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}

( PostgreSQL 9.1.11):

Driver: PostgreSQL 8.0 JDBC3 with SSL (build 313)
Parameter Name: returnValue Paramter Type: 5 Data Type: 1111
Parameter Name: $1 Paramter Type: 1 Data Type: 4

Driver: PostgreSQL 9.0 JDBC4 (build 801)
Parameter Name: itemid Paramter Type: 1 Data Type: 4
Parameter Name: id Paramter Type: 1 Data Type: 4
Parameter Name: name Paramter Type: 1 Data Type: 12

Driver: PostgreSQL 9.3 JDBC4 (build 1100)
Parameter Name: itemid Paramter Type: 1 Data Type: 4
Parameter Name: id Paramter Type: 1 Data Type: 4
Parameter Name: name Paramter Type: 1 Data Type: 12

, 8.0 5. 2- 3- 1.

, PostgreSQL 8 - JDBC3 JDBC4. , , . .