PostgreSQL Programming: C API

The PostgreSQL client "C" language API development libraries and "C" header files are available as PostgreSQL development packages. This PostgreSQL API is often referred to as "libpq".

• postgresql-8.4.11-1.el6_2.x86_64 - commands, HTML docs and man pages
• postgresql-server-8.4.11-1.el6_2.x86_64 - DB server and locale based messages
• postgresql-libs-8.4.11-1.el6_2.x86_64 - libraries and locale based messages
• postgresql-devel-8.4.11-1.el6_2.x86_64 - include files, libs and tools including a SQL to C tool (ecpg)
• postgresql-docs-8.4.11-1.el6_2.x86_64 - tutorials, examples and a monster PDF manual

• postgresql-9.1 - libraries and SQL
• postgresql-common - the database program
• postgresql-client-9.1 - utility programs and man pages
• postgresql-client-common - utility programs and man pages
• libpq5 - libpq library
• libpq-dev - Include files (/usr/include/postgresql/), libraries, pg_config

The PostgreSQL client API has been ported to many platforms with many different compilers. To help developers determine the compiling and linking flags to use on their platform use the pg_config utility program:

• C compile flags: pg_config --cflags
  Result: -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g
• Linker flags: pg_config --libs
  Result: -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lkrb5 -lcom_err -lgssapi_krb5 -lz -ledit -lcrypt -ldl -lm

• # Insert and select from an existing database and table from a C/C++ program
• # Generate a new database and table and insert a record from a C/C++ program
• # Drop a database from a C/C++ program

This example will use the following example database schema and contents:

CREATE TABLE employee (IDpk serial NOT NULL,
                       Employee_Name char(20),
                       Dept char(20),
                       JobTitle char(20),
                       PRIMARY KEY (IDpk));
INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Fred Flinstone','Worker','Rock Digger');
INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Wilma Flinstone','Finance','Analyst');
INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Barney Rubble','Sales','Neighbor');

$ sudo su - postgres
-bash-4.1$ createdb bedrock
-bash-4.1$ psql -d bedrock -a -f bedrock.sql
-bash-4.1$ psql -d bedrock -c "select * from employee"
 idpk |    employee_name     |         dept         |       jobtitle       
------+----------------------+----------------------+----------------------
    1 | Fred Flinstone       | Worker               | Rock Digger         
    2 | Wilma Flinstone      | Finance              | Analyst             
    3 | Barney Rubble        | Sales                | Neighbor            
(3 rows)

-bash-4.1$ psql bedrock
psql (8.4.11)
Type "help" for help.

bedrock=# \d employee
                                 Table "public.employee"
    Column     |     Type      |                        Modifiers                        
---------------+---------------+---------------------------------------------------------
 idpk          | integer       | not null default nextval('employee_idpk_seq'::regclass)
 employee_name | character(20) | 
 dept          | character(20) | 
 jobtitle      | character(20) | 
Indexes:
    "employee_pkey" PRIMARY KEY, btree (idpk)

File: testPostgreSqlAPI.cpp

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

/* 
 * Connect to an existing database.
 * Insert a new record for Betty.
 * Select full contents of the table and print all fields.
 */

static void
exit_nicely(PGconn *conn, PGresult   *res)
{
    PQclear(res);
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo = "dbname=bedrock sslmode=disable";
    PGconn     *conn;
    PGresult   *res;
    int         nFields;
    int         i,
                j;

    // 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));
        PQfinish(conn);
        exit(1);
    }

    res = PQexec(conn, "INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Betty Rubble','IT','Neighbor')");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }
    PQclear(res);

    // Use cursor inside a transaction block

    // Start a transaction block
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }
    PQclear(res);  // Clear memory

    res = PQexec(conn, "DECLARE mydata CURSOR FOR select * from employee");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in mydata");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }

    // first, print out the table collumn attribute names
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");

    // next, print out the rows of data
    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 mydata");
    PQclear(res);

    // End the transaction 
    res = PQexec(conn, "END");
    PQclear(res);

    // close the connection to the database and cleanup 
    PQfinish(conn);

    return 0;
}

• The query string is NOT terminated with a ";"
• PQconnectdb(): argument "conninfo" contains a keyword=value pair (spaces around "=" are optional).
  Each pair is space delimited.
  

  Keyword	Description
  host	network host
  hostaddr	IP address
  port	TPC/IP port
  dbname	database name
  user	PostgreSQL user id
  password	server authentication
  connect_timeout	max wait time. 0=infinite.
  options	server command line options
  sslmode	Six modes available (disable,allow,prefer (default),verify-ca,verify-full)
  sslcert	specify file of the client SSL certificate
  sslrootcert	specify file of the root SSL certificate
  sslcrl	specify file name of the SSL certificate revocation list (CRL)
  krbsrvname	Kerberos service name
  gsslib	MS/Windows only
  service	specify service name listed in pg_Service.conf which holds connection parameters.

  Environment variables can be used to specify each of the keyword values.

g++ -o testPostgreSqlAPI testPostgreSqlAPI.cpp `pg_config --cflags` `pg_config --libs`
or
gcc -o testPostgreSqlAPI testPostgreSqlAPI.c -lpq

-bash-4.1$ ./testPostgreSqlAPI
idpk           employee_name       dept           jobtitle       

1              Fred Flinstone      Worker              Rock Digger         
2              Wilma Flinstone     Finance             Analyst             
3              Barney Rubble       Sales               Neighbor 
4              Betty Rubble        IT                  Neighbor

[user1]$ ./testPostgreSqlDb
Connection to database failed: FATAL:  Ident authentication failed for user "user1"

This is because the permissions of the user id of the process did not have the authority to access the database. Grant access to the user in the database or run as the authorized user "postgres": sudo su - postgres

Typically when you get a connection to PostgreSQL it is always to a particular database.
To create a new PostgreSQL database, connect with no database specified.

File: genPostgreSqlDb.cpp

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

static void
exit_nicely(PGconn *conn, PGresult   *res)
{
    PQclear(res);
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo = "sslmode=disable";
    PGconn     *conn;
    PGresult   *res;

    //------------------------------------------------------------------------
    // To create a new PostgreSQL database, connect with no database specified
    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));
        PQfinish(conn);
        exit(1);
    }

    res = PQexec(conn, "CREATE DATABASE adams");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "CREATE DATABASE failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }
    PQclear(res);

    PQfinish(conn); // Close connection

    //--------------------------------------------------------
    // Generate new connection to database adams created above
    conn = PQconnectdb("dbname=adams");
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to adams database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        exit(1);
    }

    res = PQexec(conn, "CREATE TABLE family (Name char(20),Room char(8),Phone char(24))");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "CREATE TABLE failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }
    PQclear(res);

    PQfinish(conn); // Close connection

    //--------------------------------------------------------
    // Generate new connection to database adams created above
    conn = PQconnectdb("dbname=adams");
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to adams database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        exit(1);
    }

    res = PQexec(conn, "CREATE TABLE family (Name char(20),Room char(8),Phone char(24))");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "CREATE TABLE failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }
    PQclear(res);

    res = PQexec(conn, "INSERT INTO family VALUES ('Gomez Adams', 'master', '1-555-1212')");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }
    PQclear(res);

    // close the connection to the database and cleanup
    PQfinish(conn);

    return 0;
}

Compile: g++ -o genPostgreSqlDb genPostgreSqlDb.cpp `pg_config --cflags` `pg_config --libs`

-bash-4.1$ psql 
psql (8.4.11)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 adams     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 bedrock   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
(5 rows)

postgres=# \c adams
psql (8.4.11)
You are now connected to database "adams".
adams=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | family | table | postgres
(1 row)

adams=# SELECT * FROM family;
         name         |   room   |          phone           
----------------------+----------+--------------------------
 Gomez Adams          | master   | 1-555-1212              
(1 row)

One can not be connected to a database if it is to be dropped. In fact no one can be connected to it.
To drop a PostgreSQL database, connect with no database specified.

File: dropPostgreSqlDb.cpp

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

static void
exit_nicely(PGconn *conn, PGresult   *res)
{
    PQclear(res);
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo = "sslmode=disable";
    PGconn     *conn;
    PGresult   *res;

    //------------------------------------------------------------------
    // To drop a PostgreSQL database, connect with no database specified
    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));
        PQfinish(conn);
        exit(1);
    }

    res = PQexec(conn, "DROP DATABASE adams");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DROP DATABASE failed: %s", PQerrorMessage(conn));
        exit_nicely(conn,res);
    }
    PQclear(res);

    // close the connection to the database and cleanup
    PQfinish(conn);

    return 0;
}

Compile: g++ -o dropPostgreSqlDb dropPostgreSqlDb.cpp `pg_config --cflags` `pg_config --libs`

• PostgreSQL.com:
  • C API documentation

Books:

	PostgreSQL by Korry Douglas, Susan Douglas ISBN #0735712573, New Riders
	PostgreSQL Essential Reference by Barry Stinson ISBN #0735711216, New Riders
	PostgreSQL: Developer's Handbook by Ewald Geschwinde, Hans-Juergen Schoenig, Hans-Jurgen Schonig ISBN #0672322609, SAMS
	Practical PostgreSQL John C. Worsley, Joshua D. Drake ISBN #1565928466, O'Reilly
	Beginning Databases with PostgreSQL by Richard Stones, Neil Matthew ISBN #1861005156, Wrox Press Inc