/*
* 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.0, 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 failed0);
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 failed0);
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 properly0);
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("0);
/* 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("0);
}
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);
/* 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.0, dbName);
fprintf(stderr,"%s",PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexec(conn, "LISTEN TBL2");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr,"LISTEN command failed0);
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 = %s0, pgresStatus[PQresultStatus(res)]); */
/* check for asynchronous returns */
notify = PQnotifies(conn);
if (notify) {
fprintf(stderr,
"ASYNC NOTIFY of '%s' from backend pid '%d' received0,
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.0, 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 failed0);
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 failed0);
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 properly0);
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] = %d0,
i, PQftype(res,i),
i, PQfsize(res,i));
}
for (i=0; i < PQntuples(res); i++) {
int *ival;
float *dval;
int plen;
POLYGON* pval;
/*/
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: got0, i);
printf(" i = (%d bytes) %d,0,
PQgetlength(res,i,i_fnum), *ival);
printf(" d = (%d bytes) %f,0,
PQgetlength(res,i,d_fnum), *dval);
printf(" p = (%d bytes) %d points boundbox = (hi=%f/%f, lo = %f,%f)0,
PQgetlength(res,i,d_fnum),
pval->npts,
pval->boundbox.xh,
pval->boundbox.yh,
pval->boundbox.xl,
pval->boundbox.yl);
}
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);
}