PostgreSQL Programming: C API

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 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

Insert and select from an existing database:

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

File: bedrock.sql

1CREATE TABLE employee (IDpk serial NOT NULL,
2                       Employee_Name char(20),
3                       Dept char(20),
4                       JobTitle char(20),
5                       PRIMARY KEY (IDpk));
6INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Fred Flinstone','Worker','Rock Digger');
7INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Wilma Flinstone','Finance','Analyst');
8INSERT 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

001#include <stdio.h>
002#include <stdlib.h>
003#include <libpq-fe.h>
004 
005/* 
006 * Connect to an existing database.
007 * Insert a new record for Betty.
008 * Select full contents of the table and print all fields.
009 */
010 
011static void
012exit_nicely(PGconn *conn, PGresult   *res)
013{
014    PQclear(res);
015    PQfinish(conn);
016    exit(1);
017}
018 
019int
020main(int argc, char **argv)
021{
022    const char *conninfo = "dbname=bedrock sslmode=disable";
023    PGconn     *conn;
024    PGresult   *res;
025    int         nFields;
026    int         i,
027                j;
028 
029    // Make a connection to the database
030    conn = PQconnectdb(conninfo);
031 
032    // Check to see that the backend connection was successfully made
033    if (PQstatus(conn) != CONNECTION_OK)
034    {
035        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
036        PQfinish(conn);
037        exit(1);
038    }
039 
040    res = PQexec(conn, "INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Betty Rubble','IT','Neighbor')");
041    if (PQresultStatus(res) != PGRES_COMMAND_OK)
042    {
043        fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
044        exit_nicely(conn,res);
045    }
046    PQclear(res);
047 
048    // Use cursor inside a transaction block
049 
050    // Start a transaction block
051    res = PQexec(conn, "BEGIN");
052    if (PQresultStatus(res) != PGRES_COMMAND_OK)
053    {
054        fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
055        exit_nicely(conn,res);
056    }
057    PQclear(res);  // Clear memory
058 
059    res = PQexec(conn, "DECLARE mydata CURSOR FOR select * from employee");
060    if (PQresultStatus(res) != PGRES_COMMAND_OK)
061    {
062        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
063        exit_nicely(conn,res);
064    }
065    PQclear(res);
066 
067    res = PQexec(conn, "FETCH ALL in mydata");
068    if (PQresultStatus(res) != PGRES_TUPLES_OK)
069    {
070        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
071        exit_nicely(conn,res);
072    }
073 
074    // first, print out the table collumn attribute names
075    nFields = PQnfields(res);
076    for (i = 0; i < nFields; i++)
077        printf("%-15s", PQfname(res, i));
078    printf("\n\n");
079 
080    // next, print out the rows of data
081    for (i = 0; i < PQntuples(res); i++)
082    {
083        for (j = 0; j < nFields; j++)
084            printf("%-15s", PQgetvalue(res, i, j));
085        printf("\n");
086    }
087 
088    PQclear(res);
089 
090    // close the portal ... we don't bother to check for errors ...
091    res = PQexec(conn, "CLOSE mydata");
092    PQclear(res);
093 
094    // End the transaction 
095    res = PQexec(conn, "END");
096    PQclear(res);
097 
098    // close the connection to the database and cleanup 
099    PQfinish(conn);
100 
101    return 0;
102}

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.

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.

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

01#include <stdio.h>
02#include <stdlib.h>
03#include <libpq-fe>
04 
05static void
06exit_nicely(PGconn *conn, PGresult   *res)
07{
08    PQclear(res);
09    PQfinish(conn);
10    exit(1);
11}
12 
13int
14main(int argc, char **argv)
15{
16    const char *conninfo = "sslmode=disable";
17    PGconn     *conn;
18    PGresult   *res;
19 
20    //------------------------------------------------------------------------
21    // To create a new PostgreSQL database, connect with no database specified
22    conn = PQconnectdb(conninfo);
23 
24    // Check to see that the backend connection was successfully made
25    if (PQstatus(conn) != CONNECTION_OK)
26    {
27        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
28        PQfinish(conn);
29        exit(1);
30    }
31 
32    res = PQexec(conn, "CREATE DATABASE adams");
33    if (PQresultStatus(res) != PGRES_COMMAND_OK)
34    {
35        fprintf(stderr, "CREATE DATABASE failed: %s", PQerrorMessage(conn));
36        exit_nicely(conn,res);
37    }
38    PQclear(res);
39 
40    PQfinish(conn); // Close connection
41 
42    //--------------------------------------------------------
43    // Generate new connection to database adams created above
44    conn = PQconnectdb("dbname=adams");
45    if (PQstatus(conn) != CONNECTION_OK)
46    {
47        fprintf(stderr, "Connection to adams database failed: %s", PQerrorMessage(conn));
48        PQfinish(conn);
49        exit(1);
50    }
51 
52    res = PQexec(conn, "CREATE TABLE family (Name char(20),Room char(8),Phone char(24))");
53    if (PQresultStatus(res) != PGRES_COMMAND_OK)
54    {
55        fprintf(stderr, "CREATE TABLE failed: %s", PQerrorMessage(conn));
56        exit_nicely(conn,res);
57    }
58    PQclear(res);
59 
60    PQfinish(conn); // Close connection
61 
62    //--------------------------------------------------------
63    // Generate new connection to database adams created above
64    conn = PQconnectdb("dbname=adams");
65    if (PQstatus(conn) != CONNECTION_OK)
66    {
67        fprintf(stderr, "Connection to adams database failed: %s", PQerrorMessage(conn));
68        PQfinish(conn);
69        exit(1);
70    }
71 
72    res = PQexec(conn, "CREATE TABLE family (Name char(20),Room char(8),Phone char(24))");
73    if (PQresultStatus(res) != PGRES_COMMAND_OK)
74    {
75        fprintf(stderr, "CREATE TABLE failed: %s", PQerrorMessage(conn));
76        exit_nicely(conn,res);
77    }
78    PQclear(res);
79 
80    res = PQexec(conn, "INSERT INTO family VALUES ('Gomez Adams', 'master', '1-555-1212')");
81    if (PQresultStatus(res) != PGRES_COMMAND_OK)
82    {
83        fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
84        exit_nicely(conn,res);
85    }
86    PQclear(res);
87 
88    // close the connection to the database and cleanup
89    PQfinish(conn);
90 
91    return 0;
92}

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

01#include <stdio.h>
02#include <stdlib.h>
03#include <libpq-fe>
04 
05static void
06exit_nicely(PGconn *conn, PGresult   *res)
07{
08    PQclear(res);
09    PQfinish(conn);
10    exit(1);
11}
12 
13int
14main(int argc, char **argv)
15{
16    const char *conninfo = "sslmode=disable";
17    PGconn     *conn;
18    PGresult   *res;
19 
20    //------------------------------------------------------------------
21    // To drop a PostgreSQL database, connect with no database specified
22    conn = PQconnectdb(conninfo);
23 
24    // Check to see that the backend connection was successfully made
25    if (PQstatus(conn) != CONNECTION_OK)
26    {
27        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
28        PQfinish(conn);
29        exit(1);
30    }
31 
32    res = PQexec(conn, "DROP DATABASE adams");
33    if (PQresultStatus(res) != PGRES_COMMAND_OK)
34    {
35        fprintf(stderr, "DROP DATABASE failed: %s", PQerrorMessage(conn));
36        exit_nicely(conn,res);
37    }
38    PQclear(res);
39 
40    // close the connection to the database and cleanup
41    PQfinish(conn);
42 
43    return 0;
44}

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

Run: ./dropPostgreSqlDb

Links:

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