This version of the documentation describes the C interface library. Three short programs are included at the end of this section to show how to write programs that use Libpq.
There are several examples of Libpq applications in the
following directories: ../src/test/regress
../src/test/examples
../src/bin/psql
Frontend programs which use Libpq must include the header file libpq-fe.h and must link with the libpq library.
The following environment variables can be used to set up default environment values to avoid hard-coding database names into an application program:
· PGHOST sets the default server name.
· PGOPTIONS sets additional runtime options for the Postgres backend.
· PGPORT sets the default port for communicating with the Postgres backend.
· PGTTY sets the file or tty on which debugging messages from the backend server are displayed.
· PGDATABASE sets the default Postgres database name.
· PGREALM sets the Kerberos realm to use with Postgres, if it is different from the local realm. If PGREALM is set, Postgres applications will attempt authentication with servers for this realm and use separate ticket files to avoid conflicts with local ticket files. This environment variable is only used if Kerberos authentication is enabled.
The following routines deal with making a connection to a backend from a C program.
PQsetdb
PQfinish
PQreset
PQtrace
PQuntrace
PQexec
PQresultStatus
int PQntuples(PGresult
*res);
PQcmdTuples returns the number of tuples (instances) affected by INSERT, UPDATE, and DELETE queries.
char *PQcmdTuples(PGresult *res);
PQnfields returns the number of fields (attributes) in the query result.
int PQnfields(PGresult *res);
PQfname returns the field (attribute)
name associated with the given field index. Field indices start at 0. char
*PQfname(PGresult *res,
int field_index);
PQfnumber returns
the field (attribute) index associated with the given field name. int PQfnumber(PGresult
*res,
char* field_name);
PQftype returns the field type
associated with the given field index. The integer returned is an internal
coding of the type. Field indices start at 0. Oid PQftype(PGresult *res,
int field_num);
PQfsize returns the size in bytes of the
field associated with the given field index. If the size returned is -1,
the field is a variable length field. Field indices start at 0. int2 PQfsize(PGresult
*res,
int field_index);
PQgetvalue returns the field (attribute)
value. For most queries, the value returned by PQgetvalue is a null-terminated
ASCII string representation of the attribute value. If the query was a
result of a BINARY cursor, then the value returned by PQgetvalue is the
binary representation of the type in the internal format of the backend
server. It is the programmer's responsibility to cast and convert the data
to the correct C type. The value returned by PQgetvalue points to storage
that is part of the PGresult structure. One must explicitly copy the value
into other storage if it is to be used past the lifetime of the PGresult
structure itself. char* PQgetvalue(PGresult *res,
int
tup_num,
int field_num);
PQgetlength returns the length
of a field (attribute) in bytes. If the field is a struct varlena , the
length returned here does not include the size field of the varlena,
i.e., it is 4 bytes less. int PQgetlength(PGresult *res,
int tup_num,
int field_num);
PQgetisnull returns the
NULL status of a field. int PQgetisnull(PGresult *res,
int tup_num,
int field_num);
PQcmdStatus
PQoidStatus
PQprint
PQprintOpt is a typedef'ed structure as defined below. typedef struct _PQprintOpt { bool header; /* print table headings and row count */ bool align; /* fill align the fields */ bool standard; /* old brain dead format (needs align) */ bool html3; /* output html3+ tables */ bool expanded; /* expand tables */ bool pager; /* use pager if needed */ char *fieldSep; /* field separator */ char *caption; /* html table caption (or NULL) */ char **fieldName; /* null terminated array of field names (or NULL) */ } PQprintOpt;
PQclear
Postgres provides a fast
path interface to send function calls to the backend. This is a trapdoor
into system internals and can be a potential security hole. Most users
will not need this feature. PGresult* PQfn(PGconn* conn,
int fnid,
int *result_buf,
int *result_len,
int result_is_int,
PQArgBlock *args,
int nargs);
The fnid argument is the
object identifier of the function to be executed. result_buf is the buffer
in which to load the return value. The caller must have allocated sufficient
space to store the return value. The result length will be returned in
the storage pointed to by result_len. If the result is to be an integer
value, than result_is_int should be set to 1; otherwise it should be
set to 0. args and nargs specify the arguments to the function. typedef
struct {
int len;
int isint;
union {
int *ptr;
int integer;
} u;
} PQArgBlock;
PQfn always returns a valid PGresult*. The resultStatus should be checked before the result is used. The caller is responsible for freeing the PGresult with PQclear when it is not longer needed.
Postgres supports asynchronous notification via the LISTEN and NOTIFY commands. A backend registers its interest in a particular relation with the LISTEN command. All backends listening on a particular relation will be notified asynchronously when a NOTIFY of that relation name is executed by another backend. No additional information is passed from the notifier to the listener. Thus, typically, any actual data that needs to be communicated is transferred through the relation.
Libpq applications are notified whenever a connected backend has received an asynchronous notification. However, the communication from the backend to the frontend is not asynchronous. Notification comes piggy-backed on other query results. Thus, an application must submit queries, even empty ones, in order to receive notice of backend notification. In effect, the Libpq application must poll the backend to see if there is any pending notification information. After the execution of a query, a frontend may call PQNotifies to see if any notification data is available from the backend.
PQNotifies
The second sample program gives an example of the use of asynchronous notification.
The copy command in Postgres has options to read from or write to the network connection used by Libpq. Therefore, functions are necessary to access this network connection directly so applications may take full advantage of this capability.
PQgetline
PQputline
PQendcopy
PQtrace
PQuntrace
If the user has generated the appropriate authentication credentials (e.g., obtaining Kerberos tickets), the frontend/backend authentication process is handled by PQexec without any further intervention. The following routines may be called by Libpq programs to tailor the behavior of the authentication process.
fe_getauthname
fe_setauthsvc
The query buffer is 8192 bytes long, and queries over that length will be silently truncated.
/*
* testlibpq.c
* Test the C version
of Libpq, the Postgres frontend library.
*
*
*/
#include <stdio.h>
#include "libpq-fe.h"
void
exit_nicely(PGconn* conn)
{
PQfinish(conn);
exit(1)
;
}
main()
{
char *pghost, *pgport, *pgoptions, *pgtty;
char* dbName;
int nFields;
int i,j;
/* FILE *debug; */
PGconn* conn;
PGresult* res;
/* begin, by setting the parameters
for a backend connection
if the parameters are null, then the system
will try to use
reasonable defaults by looking up environment variables
or, failing that, using hardwired constants */
pghost = NULL;
/* host name of the backend server */
pgport = NULL; /* port of the
backend server */
pgoptions = NULL; /* special options to start up
the backend server */
pgtty = NULL; /* debugging tty for the backend
server */
dbName = "template1";
/* make a connection to the database
*/
conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
/*
check to see that the backend connection was successfully made */
if
(PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr,"Connection to
database '%s' failed.\n", dbName);
fprintf(stderr,"%s",PQerrorMessage(conn));
exit_nicely(conn);
}
/* debug = fopen("/tmp/trace.out","w");
*/
/* PQtrace(conn, debug); */
/* start a transaction block */
res = PQexec(conn,"BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr,"BEGIN command failed\n");
PQclear(res);
exit_nicely(conn);
}
/* should PQclear PGresult whenever it is
no longer needed to avoid
memory leaks */
PQclear(res);
/* fetch instances from the pg_database, the system catalog of databases*/
res = PQexec(conn,"DECLARE mycursor CURSOR FOR select * from pg_database");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr,"DECLARE
CURSOR command failed\n");
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn,"FETCH ALL in mycursor");
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
fprintf(stderr,"FETCH
ALL command didn't return tuples properly\n");
PQclear(res);
exit_nicely(conn);
}
/* first, print out the attribute names */
nFields = PQnfields(res);
for (i=0; i < nFields; i++) {
printf("%-15s",PQfname(res,i));
}
printf("\n\n");
/* next, print out the instances */
for (i=0;
i < PQntuples(res); i++) {
for (j=0 ; j < nFields; j++) {
printf("%-15s",
PQgetvalue(res,i,j));
}
printf("\n");
}
PQclear(res);
/* close the cursor */
res = PQexec(conn, "CLOSE mycursor");
PQclear(res);
/* end the transaction */
res = PQexec(conn, "END");
PQclear(res);
/* close the connection to the database and cleanup
*/
PQfinish(conn);
/* fclose(debug); */
}
/*
* testlibpq2.c
* Test of the asynchronous notification interface
*
populate a database with the following:
CREATE TABLE TBL1 (i
int4);
CREATE TABLE TBL2 (i int4);
CREATE RULE r1 AS ON INSERT TO
TBL1 DO [INSERT INTO TBL2 values (new.i); NOTIFY TBL2];
* Then start
up this program
* After the program has begun, do
INSERT INTO TBL1
values (10);
*
*
*/
#include <stdio.h>
#include "libpq-fe.h"
void
exit_nicely(PGconn* conn)
{
PQfinish(conn);
exit(1)
;
}
main()
{
char *pghost, *pgport, *pgoptions, *pgtty;
char* dbName;
int
nFields;
int i,j;
PGconn* conn;
PGresult* res;
PGnotify*
notify;
/* begin, by setting the parameters for a backend connection
if the parameters are null, then the system will try to use
reasonable defaults by looking up environment variables
or, failing
that, using hardwired constants */
pghost = NULL; /* host name of
the backend server */
pgport = NULL; /* port of the backend server
*/
pgoptions = NULL; /* special options to start up the backend server
*/
pgtty = NULL; /* debugging tty for the backend server */
dbName = getenv("USER"); /* change this to the name of your test database*/
/* make a connection to the database */
conn = PQsetdb(pghost,
pgport, pgoptions, pgtty, dbName);
/* check to see that the backend
connection was successfully made */
if (PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr,"Connection to database '%s' failed.\n", dbName);
fprintf(stderr,"%s",PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexec(conn, "LISTEN TBL2");
if (PQresultStatus(res)
!= PGRES_COMMAND_OK) {
fprintf(stderr,"LISTEN command failed\n");
PQclear(res);
exit_nicely(conn);
}
/* should PQclear PGresult
whenever it is no longer needed to avoid
memory leaks */
PQclear(res);
while (1)
{
/* async notification only come back as a result
of a query*/
/* we can send empty queries */
res = PQexec(conn,
"");
/* printf("res->status = %s\n", pgresStatus[PQresultStatus(res)]);
*/
/* check for asynchronous returns */
notify = PQnotifies(conn);
if (notify) {
fprintf(stderr,
"ASYNC NOTIFY of '%s' from backend
pid '%d' received\n",
notify->relname, notify->be_pid);
free(notify)
;
break;
}
PQclear(res);
}
/* close the connection
to the database and cleanup */
PQfinish(conn);
}
/*
* testlibpq3.c
* Test the C version of Libpq, the Postgres frontend
library.
* tests the binary cursor interface
*
*
*
populate
a database by doing the following:
CREATE TABLE test1 (i int4, d float4,
p polygon);
INSERT INTO test1 values (1, 3.567, '(3.0, 4.0, 1.0, 2.0)'::polygon);
INSERT INTO test1 values (2, 89.05, '(4.0, 3.0, 2.0, 1.0)'::polygon);
the
expected output is:
tuple 0: got
i = (4 bytes) 1,
d = (4 bytes)
3.567000,
p = (4 bytes) 2 points boundbox = (hi=3.000000/4.000000,
lo = 1.000000,2.000000)
tuple 1: got
i = (4 bytes) 2,
d = (4 bytes)
89.050003,
p = (4 bytes) 2 points boundbox = (hi=4.000000/3.000000,
lo = 2.000000,1.000000)
*
*/
#include <stdio.h>
#include "libpq-fe.h"
#include "utils/geo-decls.h" /* for the POLYGON type */
void exit_nicely(PGconn*
conn)
{
PQfinish(conn);
exit(1)
;
}
main()
{
char *pghost,
*pgport, *pgoptions, *pgtty;
char* dbName;
int nFields;
int i,j;
int i_fnum, d_fnum, p_fnum;
PGconn* conn;
PGresult* res;
/* begin, by setting the parameters for a backend connection
if
the parameters are null, then the system will try to use
reasonable
defaults by looking up environment variables
or, failing that,
using hardwired constants */
pghost = NULL; /* host name of the backend
server */
pgport = NULL; /* port of the backend server */
pgoptions
= NULL; /* special options to start up the backend server */
pgtty
= NULL; /* debugging tty for the backend server */
dbName = getenv("USER");
/* change this to the name of your test database*/
/* make a connection
to the database */
conn = PQsetdb(pghost, pgport, pgoptions, pgtty,
dbName);
/* check to see that the backend connection was successfully
made */
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr,"Connection
to database '%s' failed.\n", dbName);
fprintf(stderr,"%s",PQerrorMessage(conn));
exit_nicely(conn);
}
/* start a transaction block */
res
= PQexec(conn,"BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr,"BEGIN command failed\n");
PQclear(res);
exit_nicely(conn);
}
/* should PQclear PGresult whenever it is
no longer needed to avoid
memory leaks */
PQclear(res);
/* fetch instances from the pg_database, the system catalog of databases*/
res = PQexec(conn,"DECLARE mycursor BINARY CURSOR FOR select * from
test1");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr,"DECLARE
CURSOR command failed\n");
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn,"FETCH ALL in mycursor");
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
fprintf(stderr,"FETCH
ALL command didn't return tuples properly\n");
PQclear(res);
exit_nicely(conn);
}
i_fnum = PQfnumber(res,"i");
d_fnum = PQfnumber(res,"d");
p_fnum = PQfnumber(res,"p");
for (i=0;i<3;i++) {
printf("type[%d]
= %d, size[%d] = %d\n",
i, PQftype(res,i),
i, PQfsize(res,i));
}
for (i=0; i < PQntuples(res); i++) {
int *ival;
float
*dval;
int plen;
POLYGON* pval;
/* we hard-wire this to
the 3 fields we know about */
ival = (int*)PQgetvalue(res,i,i_fnum);
dval = (float*)PQgetvalue(res,i,d_fnum);
plen = PQgetlength(res,i,p_fnum);
/* plen doesn't include the length field so need to increment by
VARHDSZ*/
pval = (POLYGON*) malloc(plen + VARHDRSZ);
pval->size
= plen;
memmove((char*)&pval->npts, PQgetvalue(res,i,p_fnum), plen);
printf("tuple %d: got\n", i);
printf(" i = (%d bytes) %d,\n",
PQgetlength(res,i,i_fnum), *ival);
printf(" d = (%d bytes) %f,\n",
PQgetlength(res,i,d_fnum), *dval);
printf(" p = (%d bytes) %d
points \tboundbox = (hi=%f/%f, lo = %f,%f)\n",
PQgetlength(res,i,d_fnum),
pval->npts,
pval->boundbox.xh,
pval->boundbox.yh,
pval->boundbox.xl,
pval->boundbox.yl);
}
PQclear(res);
/* close the cursor
*/
res = PQexec(conn, "CLOSE mycursor");
PQclear(res);
/* end
the transaction */
res = PQexec(conn, "END");
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
}