Examples

As an example, look at the following C application:

 

        
        /*
         *  libpq sample program
         */
         
        #include <stdio.h>
        #include <stdlib.h>
        #include "libpq-fe.h"                                   /* libpq header file */
         
        int
        main()
        {
            char        state_code[3];                          /* holds user state code */
            char        query_string[256];                      /* holds constructed SQL query */
            PGconn     *conn;                                   /* holds database connection */
            PGresult   *res;                                    /* holds query result */
            int         i;
         
            conn = PQconnectdb("dbname=test");                  /* connect to the database */
         
            if (PQstatus(conn) == CONNECTION_BAD)               /* did the connection fail? */
            {
                fprintf(stderr, "Connection to database failed.\n");
                fprintf(stderr, "%s", PQerrorMessage(conn));
                exit(1);
            }
         
            printf("Enter a state code:  ");                    /* prompt user for a state code */
            scanf("%2s", state_code);
         
            sprintf(query_string,                               /* create an SQL query string */
                    "SELECT name \
                     FROM statename \
                     WHERE code = '%s'", state_code);
         
            res = PQexec(conn, query_string);                   /* send the query */
         
            if (PQresultStatus(res) != PGRES_TUPLES_OK)         /* did the query fail? */
            {
                fprintf(stderr, "SELECT query failed.\n");
                PQclear(res);
                PQfinish(conn);
                exit(1);
            }
         
            for (i = 0; i < PQntuples(res); i++)                /* loop through all rows returned */
                printf("%s\n", PQgetvalue(res, i, 0));          /* print the value returned */
         
            PQclear(res);                                       /* free result */
         
            PQfinish(conn);                                     /* disconnect from the database */
         
            return 0;
        }
         

This example is described in the Interfaces chapter of my book, PostgreSQL: Introduction and Concepts. Please review the application details at http://www.postgresql.org/docs/awbook.html if you are unfamiliar with it.

In the above program, connection/disconnection to the database is processed by the lines in red, a query is issued and the result cleared in blue, and the result is accessed in green. The conn structure holds connection information, and res holds result information. As you can see from the colors, conn is used to obtain res, and res is used to access results. You can see the following TCL program follows the same pattern. It even uses conn and res in the same way:

 

        
        #!/usr/local/pgsql/bin/pgtclsh
        #
        #   pgtclsh sample program
        #
         
        set conn [pg_connect -conninfo "dbname=test"]           ;# connect to the database
         
        puts -nonewline "Enter a state code:  "                 ;# prompt user for a state code
        flush stdout
        gets stdin state_code
                                                                ;# send the query
        set res [pg_exec $conn \
                "SELECT name \
                 FROM statename \
                 WHERE code = '$state_code'"]
         
        set ntups [pg_result $res -numTuples]
         
        for {set i 0} {$i < $ntups} {incr i} {                  ;# loop through all rows returned
                puts stdout [lindex [pg_result $res -getTuple $i] 0]    ;# print the value returned
         
        }
        pg_disconnect $conn                                     ;# disconnect from the database
         

Fancier configurations are possible. For example, you can create multiple connection handles by issuing multiple connection requests, even to different databases or as different users. You can also skip clearing results and use them later in your application. Of course, results are static. They represent the result at the time the query was executed. LIbpq even has an asynchronous set of library calls that allow multiple queries to be sent and retrieved simultaneously.