skip to main content
DataDirect Connect XE Drivers : Greenplum Driver : Parameter Metadata Support : Stored Procedure Results
  
Stored Procedure Results
Greenplum provides functionality to create user-defined functions. Greenplum does not make a distinction between user-defined functions and stored procedures. To Greenplum, everything is a user-defined function. Greenplum does not define a call mechanism for invoking a user-defined function. User-defined functions must be invoked via a SQL statement. For example, given a function defined as:

CREATE table foo (intcol int, varcharcol varchar(123))
CREATE or REPLACE FUNCTION insertFoo
  (IN idVal int, IN nameVal varchar) RETURNS void
  AS $$
    insert into foo values ($1, $2);
  $$
  LANGUAGE SQL;
must be invoked natively as:

SELECT * FROM insertFoo(100, 'Mark')
even though the function does not return a value or results. The Select SQL statement returns a result set that has one column named insertFoo and no row data.
The Greenplum driver supports invoking user-defined functions using the JDBC call Escape. The previously described function can be invoked using:

{call insertFoo(100, 'Mark')}
Greenplum functions return data from functions as a result set. If multiple output parameters are specified, the values for the output parameters are returned as columns in the result set. For example, the function defined as:

CREATE or REPLACE FUNCTION addValues(in v1 int, in v2 int)
  RETURNS int
  AS $$
    SELECT $1 + $2;
  $$
  LANGUAGE SQL;
returns a result set with a single column of type INTEGER, whereas the function defined as:

CREATE or REPLACE FUNCTION selectFooRow2
  (IN idVal int, OUT id int, OUT name varchar)
  AS $$
    select intcol, varcharcol from foo where intcol = $1;
  $$
  LANGUAGE SQL
returns a result set that contains two columns, a INTEGER id column and a VARCHAR name column.
In addition, when calling Greenplum functions that contain output parameters, the native syntax requires that the output parameter values be omitted from the function call. This, in addition to output parameter values being returned as a result set, makes the Greenplum behavior of calling functions different from most other databases.
Note: When using the ?= version of the call escape on a function that returns a set of values, only the first result in the set will be returned.
The Greenplum driver provides a mechanism that makes the invoking of functions more consistent with how other databases behave. In particular, the Greenplum driver allows parameter markers for output parameters to be specified in the function argument list when the Escape call is used. The driver extracts the output parameter values from the result set returned by the server and makes the values available via the getxxx methods on a CallableStatement.
For example, the function selectFooRow2 described previously can be invoked as:

sql = "{call selectFooRow2 (?,?,?)}";
CallableStatement cSTMT = connection.prepareCall(sql);
cSTMT.setInt(1, idVal);
cSTMT.registerOutParameter(2, Types.INTEGER);
cSTMT.registerOutParameter(3, Types.VARCHAR);
cSTMT.execute();
int myID = cSTMT.getInt(2);
String myName = cSTMT.getString(3);
The values of the id and name output parameters are returned in the myID and myName variables.
An error is returned if the number of output parameters registered when the function is executed is less than the number of output parameters defined in the function. If no output parameters are registered to a function call, the driver returns the output parameters as a result set.
Greenplum can also return results from a function as a refcursor. There can be, at most, one refcursor per result; however, a function can return multiple results where each result is a refcursor. See Ref Cursors for more information.