Unable to run POSTGRESQL stored procedure/function inside Spotfire Information link using a procedure element.

Unable to run POSTGRESQL stored procedure/function inside Spotfire Information link using a procedure element.

Description:

Unable to run POSTGRESQL stored procedure/function inside Spotfire Information link using a procedure element. For example, we have a POSTGRESQL database stored procedure as shown below which accepts one input parameter and returns two output parameters:

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;
 
When we use the above stored procedure/function inside Spotfire Professional Client Information Designer, we are presented with all the returned columns as input parameters, along with the actual input parameters. Example, with 3 IN Parameters below.

personid (the parameter)

person (first column returned) 

name (second column returned)).
 

Since Spotfire is inserting extra input parameter(s) into the database function, the database cannot run the function as these parameters do not exist.

Symptoms:
Procedure element shows the return parameters in input parameter list of Information Designer Procedure Element.

Cause:
This is a known issue in an older version of the POSTGRESQL driver .
Related link : http://stackoverflow.com/questions/21541745/postgresql-9-jdbc-driver-returns-incorrect-metadata-for-stored-procedures

Download driver : postgresql-9.4-1201.jdbc41.jar

Link to download : https://jdbc.postgresql.org/download.html

1). Download the latest driver of version 9.4 using the above link.

2). Replace the driver at location <installationDIR>\tibco\tss\x.x.0\tomcat\lib

3). Save the configuration for data source and restart the Spotfire services.

http://stackoverflow.com/questions/21541745/postgresql-9-jdbc-driver-returns-incorrect-metadata-for-stored-procedures