PostgreSQL 9 JDBC driver returns incorrect metadata for stored procedures

I am on a team that develops a business intelligence (reporting) tool. We report off many sources include Stored Procedures. We use the meta-data provided by the JDBC driver to determine the input and output paramters of a Stored Procedure.

It appears that PostgreSQL 9 JDBC drivers are incorrectly returning meta-data for the parameters of a procedure.

For instance 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 paramter in, two columns returned in a resultset.

The PostgreSQL driver is reporting that there are 3 IN parameters.

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

with no meta-data to distinguish between types.

I execute this with:

SELECT * FROM person(?);

(As a prepared statement, setting values for each ? token)

I know that I can filter using the columns returned like this:

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

But I am more interested in getting only the parameters returned by the meta-data, so I can programmatically build the query string (I need to know how many ?'s to put in the query).

Not sure if this is a bug, or whether I am doing something wrong.

If I use a PostgreSQL 8 driver, it seems to return the correct number of parameters:

  • personid (the parameter)

Thanks.

Specific Driver/Server versions are:

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

Additional Information to replicate what I am seeing:

DB Scripts:

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 Code:

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();
        }

    }

}

Output with different drivers (all against PostgreSQL 9.1.11 server):

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

Note that the 8.0 Driver flags the return value as Type 5 .. all other drivers flag the 2nd and 3rd paramters as Type 1.

Obviously the PostgreSQL 8 driver is JDBC3 and the others JDBC4. If this is the reason that the results are different then that's great.. but I still want to distinguish between an actual input paramter and output parameter.

Answers


There was a issue in the PostgreSQL JDBC driver. Building the driver from the lastest PostgreSQL JDBC driver source code returned the correct meta-data for the Stored Procedure.

Driver: PostgreSQL 9.4 JDBC4.1 (build 1200)
Parameter Name: itemid Paramter Type: 1 Data Type: 4
Parameter Name: id Paramter Type: 5 Data Type: 4
Parameter Name: name Paramter Type: 5 Data Type: 12

Need Your Help

Changing Segments Alters Cell Height - How do I revert back?

objective-c ios7 xcode5 custom-cell

I have a UISegmentedControl with 2 segments. I'm loading JSON data into a table view and each segment has different data. I first load the view and the default first segment shows up - data is th...

Iconic Tile BackgroundColor not working when set in WMAppManifest

c# windows-phone-8 windows-phone

When settings the background color in the WMAppManifest like following example the phone themes color is still used.