Stored Procedure Results

PostgreSQL provides functionality to create user-defined functions. PostgreSQL does not make a distinction between user-defined functions and stored procedures. To PostgreSQL, everything is a user-defined function. PostgreSQL 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 PostgreSQL Wire Protocol driver supports invoking user-defined functions using the ODBC call Escape. The previously described function can be invoked using:

{call insertFoo(100, 'Mark')}

PostgreSQL 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 SQL_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 SQL_INTEGER id column and a SQL_VARCHAR name column.

In addition, when calling PostgreSQL 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 PostgreSQL behavior of calling functions different from most other databases.

The PostgreSQL Wire Protocol driver provides a mechanism that makes the invoking of functions more consistent with how other databases behave. In particular, the PostgreSQL Wire Protocol driver allows parameter markers for output parameters to be specified in the function argument list when the Escape call is used. The driver allows buffers to be bound to these output parameters. When the function is executed, the output parameters are removed from the argument list sent to the server. The driver extracts the output parameter values from the result set returned by the server and updates the bound output parameter buffers with those values. For example, the function selectFooRow2 described previously can be invoked as:

sql = L"{call selectFooRow2(?, ?, ?)}";

retVal = SQLPrepare(hPrepStmt, sql, SQL_NTS);

retVal = SQLBindParameter(

  hPrepStmt, 1, SQL_PARAM_INPUT, SQL_C_LONG,

  SQL_INTEGER, 0, 0, &idBuf, 0, &idInd);

retVal = SQLBindParameter(

  hPrepStmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG,

  SQL_INTEGER, 0, 0, &idBuf2, 4, &idInd2);

retVal = SQLBindParameter(

  hPrepStmt, 3, SQL_PARAM_OUTPUT, SQL_C_WCHAR,

  SQL_VARCHAR, 30, 0, &nameBuf, 123, &nameInd);

retVal = SQLExecute(hPrepStmt);

The values of the id and name output parameters are returned in the idBuf2 and nameBuf buffers.

If output parameters are bound to a function call, the driver returns the output parameters in the bound buffers. An error is returned if the number of output parameters bound when the function is executed is less than the number of output parameters defined in the function. If no output parameters are bound to a function call, the driver returns the output parameters as a result set.

PostgreSQL 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. A connection option defines how the driver handles refcursors. See “Fetch Ref Cursors” for details about this option.