Example 1-1. libpq Example Program 1
/*
* testlibpq.c
* Test the C version of LIBPQ, the POSTGRES frontend library.
*/
#include <stdio.h>
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main()
{
char *pghost,
*pgport,
*pgoptions,
*pgtty;
char *dbName;
int nFields;
int i,
j;
#ifdef DEBUG
FILE *debug;
#endif /* DEBUG */
PGconn *conn;
PGresult *res;
/*
* Begin by setting the parameters for a backend connection.
* If the parameters are NULL, the system tries 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; /* 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);
}
#ifdef DEBUG
debug = fopen("/tmp/trace.out", "w");
PQtrace(conn, debug);
#endif /* 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 myportal 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 myportal");
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 portal */
res = PQexec(conn, "CLOSE myportal");
PQclear(res);
/* end the transaction */
res = PQexec(conn, "END");
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
#ifdef DEBUG
fclose(debug);
#endif /* DEBUG */
return 0;
} |
Example 1-2. libpq Example Program 2
/*
* 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 <stdlib.h>
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
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, 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; /* options to start up the backend server */
pgtty = NULL; /* debugging tty for the backend server */
dbName = getenv("USER"); /* change this to your test database name */
/* 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", PQresStatus(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);
return 0; /* Though PQfinish(conn1) has called
* exit(1) */
} |
Example 1-3. libpq Example Program 3
/*
* 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 "postgres.h" /* -> "c.h" -> int16, in access/attnum.h */
#include "libpq-fe.h"
#include "utils/geo_decls.h" /* for the POLYGON type */
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main()
{
char *pghost,
*pgport,
*pgoptions,
*pgtty;
char *dbName;
/*
* int nFields; int i, j;
*/
int i;
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 (res == NULL ||
PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR command failed\n");
if (res)
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "FETCH ALL in mycursor");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
if (res)
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.high.x,
pval->boundbox.high.y,
pval->boundbox.low.x,
pval->boundbox.low.y);
}
PQclear(res);
/* close the portal */
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);
return 0; /* Though PQfinish(conn1) has called
* exit(1) */
} |