libpq is the C application programmer’s interface to Advanced Server. libpq is a set of library functions that allow client programs to pass queries to the Advanced Server and to receive the results of these queries.
libpq is also the underlying engine for several other EnterpriseDB application interfaces including those written for C++, Perl, Python, Tcl and ECPG. So some aspects of libpq’s behavior will be important to the user if one of those packages is used.
Client programs that use libpq must include the header file libpq-fe.h and must link with the libpq library.
Using libpq with EnterpriseDB SPL
The EnterpriseDB SPL language can be used with the libpq interface library, providing support for:
Procedures, functions, packages
structs and typedefs
DML and DDL operations
IN/OUT/IN OUT parameters
In earlier releases, Advanced Server provided support for REFCURSORs through the following libpq functions; these functions should now be considered deprecated:
You may now use PQexec() and PQgetvalue() to retrieve a REFCURSOR returned by an SPL (or PL/pgSQL) function. A REFCURSOR is returned in the form of a null-terminated string indicating the name of the cursor. Once you have the name of the cursor, you can execute one or more FETCH statements to retrieve the values exposed through the cursor.
Please note that the samples that follow do not include error-handling code that would be required in a real-world client application.
Returning a Single REFCURSOR
The following example shows an SPL function that returns a value of type REFCURSOR:
This function expects a single parameter, p_deptno, and returns a REFCURSOR that holds the result set for the SELECT query shown in the OPEN statement. The OPEN statement executes the query and stores the result set in a cursor. The server constructs a name for that cursor and stores the name in a variable (named result). The function then returns the name of the cursor to the caller.
To call this function from a C client using libpq, you can use PQexec() and PQgetvalue():
The code sample contains a line of code that calls the getEmployees() function, and returns a result set that contains all of the employees in department 10:
The PQexec() function returns a result set handle to the C program. The result set will contain exactly one value; that value is the name of the cursor as returned by getEmployees().
Once you have the name of the cursor, you can use the SQL FETCH statement to retrieve the rows in that cursor. The function fetchAllRows() builds a FETCH ALL statement, executes that statement, and then prints the result set of the FETCH ALL statement.
The output of this program is shown below:
Returning Multiple REFCURSORs
The next example returns two REFCURSORs:
The first REFCURSOR contains the name of a cursor (employees) that contains all employees who work in a department within the range specified by the caller.
The second REFCURSOR contains the name of a cursor (departments) that contains all of the departments in the range specified by the caller.
In this example, instead of returning a single REFCURSOR, the function returns a SETOF REFCURSOR (which means 0 or more REFCURSORS). One other important difference is that the libpq program should not expect a single REFCURSOR in the result set, but should expect two rows, each of which will contain a single value (the first row contains the name of the employees cursor, and the second row contains the name of the departments cursor).
As in the previous example, you can use PQexec() and PQgetvalue() to call the SPL function:
If you call getEmpsAndDepts(20, 30), the server will return a cursor that contains all employees who work in department 20 or 30, and a second cursor containing the description of departments 20 and 30.
Advanced Server's array binding functionality allows you to send an array of data across the network in a single round-trip. When the back end receives the bulk data, it can use the data to perform insert or update operations.
Perform bulk operations with a prepared statement; use the following function to prepare the statement:
Details of PQprepare() can be found in the prepared statement section.
The following functions can be used to perform bulk operations:
PQBulkStart() initializes bulk operations on the server. You must call this function before sending bulk data to the server. PQBulkStart() initializes the prepared statement specified in stmtName to receive data in a format specified by paramFmts.
PQexecBulk() is used to supply data (paramValues) for a statement that was previously initialized for bulk operation using PQBulkStart().
This function can be used more than once after PQBulkStart() to send multiple blocks of data. See the example for more details.
This function completes the current bulk operation. You can use the prepared statement again without re-preparing it.
Alternatively, you can use the PQexecBulkPrepared() function to perform a bulk operation with a single function call. PQexecBulkPrepared() sends a request to execute a prepared statement with the given parameters, and waits for the result. This function combines the functionality of PQbulkStart(), PQexecBulk(), and PQBulkFinish(). When using this function, you are not required to initialize or terminate the bulk operation; this function starts the bulk operation, passes the data to the server, and terminates the bulk operation.
Specify a previously prepared statement in the place of stmtName. Commands that will be used repeatedly will be parsed and planned just once, rather than each time they are executed.
Example Code (Using PQBulkStart, PQexecBulk, PQBulkFinish)
The following example uses PGBulkStart, PQexecBulk, and PQBulkFinish.