Stored Procedures
Stored procedures are encapsulated by the class
RWDBStoredProc. The implementation of stored procedures in the DB Access Module for PostgreSQL provides full support for PostgreSQL user-defined functions written in PL/pgSQL or SQL languages. This includes support for creating and dropping functions, retrieving function text and parameters, executing functions, and processing result sets.
Refcursors in PL/pgSQL language functions are supported.
Composite return types in SQL language functions are not supported.
Creating a Stored Procedure
Stored procedures can be created by calling the
createProcedure() method of
RWDBDatabase. The DB Interface Module will create the parameter list from a schema you supply, while the body of the stored procedure is supplied by the application. Here is a simple example of creating a PostgreSQL stored procedure using the DB Interface Module. This example takes a string and an integer and inserts a new row into the table, if the string is not already in the table.
Example 4 – Creating a stored procedure
RWCString body =
"RETURNS BOOL AS ' \n"
" DECLARE \n"
" empname ALIAS FOR $1; \n"
" payscale ALIAS FOR $2; \n"
" foundname varchar; \n"
"BEGIN \n"
" select into foundname name from employee where name = empname; \n"
" IF NOT FOUND THEN \n"
" insert into employee values(empname, payscale); \n"
" RETURN true; \n"
" END IF; \n"
" RETURN false; \n"
"END; \n"
"' LANGUAGE 'plpgsql'";
RWDBSchema params;
RWDBColumn col1, col2;
col1.type(RWDBValue::String);
col1.storageLength(50);
col1.paramType(RWDBColumn::inParameter);
params.appendColumn(col1);
col2.type(RWDBValue::Int);
col2.paramType(RWDBColumn::inParameter);
params.appendColumn(col2);
db.createProcedure("add_employee", body, params);
Executing a Stored Procedure
The DB Access Module for PostgreSQL uses the "
select * from procName(<args>)" SQL syntax to execute stored procedures. Result sets composed of complex types written in the SQL stored procedure language are not supported. However, the refcursor type in the PL/pgSQL language is supported. Execution is accomplished by using the above syntax along with the SQL statement
FETCH to retrieve the results. The return value of the stored procedure is fetched as part of the result set. Here is an example executing the procedure created in
Example 4 .
Example 5 – Executing a stored procedure
RWDBStoredProc add_employee = aDb.storedProc("add_employee");
RWCString newname("Sarah");
int payscale = 5;
add_employee << newname << payscale;
RWDBTable results = add_employee.execute().table();
RWDBReader rdr = results.reader();
while (rdr()) {
// save or display results
}
Instantiating an RWDBStoredProc Using Schema Data
The member functions:
RWDBDatabase::storedProc(RWCString, RWDBSchema, RWDBColumn)
RWDBDatabase::storedProc(RWCString, RWDBConnection, RWDBSchema,
RWDBColumn)
increase the performance of stored procedures by eliminating the usual parameter fetch required for instantiations. The schema must include the name and type of each parameter in the stored procedure. The RWDBColumn parameter is ignored as the return value of the stored procedure is fetched as part of the result set.