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
- 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
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
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;
}
- 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.
Environment variables can be used to specify each of the keyword values.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.
Compile:
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
[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
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: ./genPostgreSqlDbCheck 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)
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
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
