pgldtest.c

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"

static void exit_nicely(PGconn *conn)
{
   PQfinish(conn);
   exit(1);
}

void check_error(PGconn *conn, PGresult *res, ExecStatusType rc, char *msg)
{
   if (PQresultStatus(res) != rc)
   {
       /* fprintf(stderr, msg, PQerrorMessage(conn)); */
       fprintf(stderr, "%s: %s\n", msg, PQerrorMessage(conn));
       PQclear(res);
       exit_nicely(conn);
   }
}

int main(int argc, char **argv)
{
   const char *conninfo;
   PGconn     *conn;
   PGresult   *res;
   int         nFields;
   int         i,
               j;
       Oid paramTypes[10];

   /*
    * If the user supplies a parameter on the command line, use it as the
    * conninfo string; otherwise default to setting dbname=postgres and using
    * environment variables or defaults for all other connection parameters.
    */
   if (argc > 1)
       conninfo = argv[1];
   else
       conninfo = "dbname = postgres";

   /* Make a connection to the database */
   conn = PQconnectdb(conninfo);

   /* Check to see that the backend connection was successfully made */
   if (PQstatus(conn) != CONNECTION_OK)
   {
       fprintf(stderr, "Connection to database failed: %s"
         , PQerrorMessage(conn));
       exit_nicely(conn);
   }

   /* my test case... */
   res = PQprepare(conn, "x"
    , "DECLARE myportal CURSOR FOR select * from pg_database"
    , 0, paramTypes);
   check_error(conn, res, PGRES_COMMAND_OK, "Prepare failed");

   /*
    * Our test case here involves using a cursor, for which we must be inside
    * a transaction block.  We could do the whole thing with a single
    * PQexec() of "select * from pg_database", but that's too trivial to make
    * a good example.
    */

   /* Start a transaction block */
   res = PQexec(conn, "BEGIN");
   check_error(conn, res, PGRES_COMMAND_OK, "BEGIN command failed");

   /*
    * Should PQclear PGresult whenever it is no longer needed to avoid memory
    * leaks
    */
   PQclear(res);

   /*
    * Fetch rows from pg_database, the system catalog of databases
    */
   res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
   check_error(conn, res, PGRES_COMMAND_OK, "DECLARE CURSOR failed");
   PQclear(res);

   res = PQexec(conn, "FETCH ALL in myportal");
   check_error(conn, res, PGRES_TUPLES_OK, "FETCH ALL failed");

   /* 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 rows */
   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 ... we don't bother to check for errors ... */
   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);

   return 0;
}