Unsupported versions: 6.3
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.
PostgreSQL
Prev Chapter 44. libpq Next

Sample Programs

Sample Program 1

         /*
          * 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); */
         }

Sample 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 "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);

         }

Sample 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 <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);

         }

Prev Home Next
BUGS Up pgtcl