1. Home
  2. Tutorials
  3. C/C++
  4. PostgreSQL C Programming API
Yolinux.com Tutorial

PostgreSQL Programming: C API

This tutorial shows the use of the PostgreSQL "C" programming API "libpq", to call native PostgreSQL "C" functions to access the database.

For an introduction to installation, configuration and use of the PostgreSQL database see the YoLinux PostgreSQL tutorial.

PostgreSQL C Language 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".

RHEL6 installed RPMs:
  • 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
Ubuntu 12.04 packages:
  • 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

Example PostgreSQL C API program:

Two examples:
Insert and select from an existing database:

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

File: bedrock.sql
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');

Load and verify:
$ 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)

Example C/C++ program:

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;
}
Note:
  • 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.
    KeywordDescription
    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.

Compile:

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

Run: (as user postgres) ./testPostgreSqlAPI
-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

[Potential Pitfall]: If you get the following error:
[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

Generate a new database and table and insert a record:

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`

Run: ./genPostgreSqlDb

Check PostgreSQL for the database, table and data:
-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)

Drop a database:

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`

Run: ./dropPostgreSqlDb

Links:

Books:

PostgreSQL
by Korry Douglas, Susan Douglas
ISBN #0735712573, New Riders

Amazon.com
PostgreSQL Essential Reference
by Barry Stinson
ISBN #0735711216, New Riders

Amazon.com
PostgreSQL: Developer's Handbook
by Ewald Geschwinde, Hans-Juergen Schoenig, Hans-Jurgen Schonig
ISBN #0672322609, SAMS

Amazon.com
Practical PostgreSQL
John C. Worsley, Joshua D. Drake
ISBN #1565928466, O'Reilly

Amazon.com
Beginning Databases with PostgreSQL
by Richard Stones, Neil Matthew
ISBN #1861005156, Wrox Press Inc

Amazon.com