C Language Interface (LIBPQ)

LIBPQ is the native C interface to POSTGRESQL. Psql  and most other interfaces use it internally for database access.

Figure  shows how LIBPQ is used.

The application code communicates with the user's terminal and uses LIBPQ for database access. It turn, LIBPQ sends queries to the database server and retrieves results.

Figure  shows the sample program using LIBPQ to access POSTGRESQL.  

        
        /*
         *  libpq sample program
         */
         
        #include <stdio.h>
        #include <stdlib.h>
        #include "libpq-fe.h"                                   /* libpq header file */
         
        int
        main()
        {
            char        state_code[3];                          /* holds state code entered by user */
            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 database 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;
        }
         

The sample program performs the following tasks:

• Establish a database connection
• Prompt for and read the state code
• Form an appropriate SQL query
• Pass the SQL query to LIBPQ
• Have POSTGRESQL execute the query
• Retrieve the query results from LIBPQ
• Display the results to the user
• Terminate the database connection

All interactions with the database are accomplished via LIBPQ functions. The following LIBPQ functions are called by the sample program: 

PQconnectdb() 

Connect to the database.

PQexec() 

Send the query to the database.

PQntuples() 

Return the number of rows (tuples) in the result.

PQgetvalue() 

Return a specific row and column of the result.

PQclear() 

Free resources used by the result.

PQfinish() 

Close the database connection. 

These functions are the most common LIBPQ functions. The Programmer's Manual covers all of this interface's functions and shows additional examples.