libpq is the C application programmer’s interface to EDB Postgres Advanced Server. libpq is a set of library functions that allow client programs to pass queries to the EDB Postgres Advanced Server and to receive the results of these queries.
libpq is also the underlying engine for several other EDB application interfaces, including those written for C++, Perl, Python, Tcl, and ECPG. Some aspects of libpq’s behavior are important to you if you are using one of those packages.
Client programs that use libpq must include the header file libpq-fe.h and must link with the libpq library.
Using libpq with EDB SPL
You can use the EDB SPL language 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, EDB Postgres Advanced Server provided support for REFCURSORs through the following libpq functions. These functions are now deprecated:
You can 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.
The examples that follow don't include the error-handling code required in a real-world client application.
Returning a single REFCURSOR
This 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 contains one value: the name of the cursor 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:
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 in 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 must not expect a single REFCURSOR in the result set. It must expect two rows, each of which contains 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 returns 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.
Binding arrays of parameters
EDB Postgres 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:
You can find details of PQprepare() in the prepared statement section.
You can use the following functions 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.
Use PQexecBulk() to supply data (paramValues) for a statement that was previously initialized for bulk operation using PQBulkStart().
You can use this function more than once after PQBulkStart() to send multiple blocks of data.
This function completes the current bulk operation. You can use the prepared statement again without preparing it again.
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 don't need 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 are used repeatedly are parsed and planned just once, rather than each time they're executed.
Using PQBulkStart, PQexecBulk, PQBulkFinish
This example uses PGBulkStart, PQexecBulk, and PQBulkFinish: