libpq

C application programmer's interface to PostgreSQL

History

Well, about when postsgresql started.

FAQ

What is it?

Database connector to Postgresql

How cool is it?

Rather.

Competitors?

hmm.

When to use it?

When you want to connect your c code with Postgresql.

Is it dead?

Absolutely not.

Where is it?

https://www.postgresql.org/docs/current/static/libpq.html

How to use it

The documentation is extensive.

Download

file: 1_download.sh

#!/bin/bash

mkdir -p tmp
cd ./tmp
wget -q https://ftp.postgresql.org/pub/source/v9.6.2/postgresql-9.6.2.tar.gz
tar -xaf postgresql-9.6.2.tar.gz
cd  postgresql-9.6.2
./configure
make

Init

Connect to your postgresql server and create as simple table.

file: create_table.sql

CREATE TABLE IF NOT EXISTS foo (
    id SERIAL PRIMARY KEY,
    ed INTEGER,
    t TIMESTAMP DEFAULT now(),
    bar VARCHAR(32),
    listint integer[]

) WITH OIDS ;

Now let's us test 3 features: async, prepared statements, and RETURNING.

file: main.c

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <sys/types.h>
#include <libpq-fe.h>

#define OUTPUT_AS_BINARY 1
#define OUTPUT_AS_TEXT 0

#define OUTPUT_RESULTS OUTPUT_AS_TEXT

void print_result_set(PGresult *result) {
    int row, col, fields, records;

    records = PQntuples(result);
    for (row =0; row < records; row++) {
        fields = PQnfields(result);
        printf("Record: %d\n" , row);
        for (col = 0; col < fields; col++ ) {
            printf("\t%s:\t%s\n", PQfname(result, col), PQgetvalue(result, row, col));
        }
    }

    printf("\n");
}

int is_result_ready(PGconn * connection) {
    int my_socket;
    struct timeval timer;
    fd_set read_mask;

    if (PQisBusy(connection) == 0) {
        return 1;
    }
    my_socket = PQsocket (connection);
    timer.tv_sec = (time_t) 1;
    timer.tv_usec =0;
    FD_ZERO (&read_mask);
    FD_SET(my_socket, &read_mask);
    if (select(my_socket + 1, &read_mask, NULL, NULL, &timer) == 0 ) {
        return 0;
    } else if (FD_ISSET(my_socket, &read_mask)) {
        PQconsumeInput(connection);
        if (PQisBusy (connection) ==0 ) {
            return 1;
        } else {
            return 0;
        }
    } else {
        return 0;
    }
}

int process_query(PGconn * connection) {
    int submitted;
    PGresult * result;
#ifdef PARAMS
    const char *query_text = "INSERT INTO foo (ed, bar) VALUES ($1, $2) RETURNING *;";
    const char *paramValues[PARAMS];
    int         paramLengths[PARAMS], i;

    paramValues[0] = "6";
    paramValues[1] = "Beatnuts`, no escapin' this!";
    for (i = 0; i <PARAMS; i++) {
        paramLengths[i] = strlen(paramValues[i]);
    }
    submitted = PQsendQueryParams( connection, query_text, PARAMS, NULL, paramValues, paramLengths, NULL, OUTPUT_RESULTS);
#else
    const char *query_text = "INSERT INTO foo (ed, bar) VALUES (6, \'cool\') RETURNING *;";
    submitted = PQsendQuery( connection, query_text);
#endif
    printf("%s submit: %d\n", query_text, submitted);
    if (submitted == 0 ) {
        printf ("%d\n", PQerrorMessage(connection));
        return;
    }
    do {
        while (is_result_ready( connection) == 0 ) {
            putchar ('.');
            fflush (stdout);
        }
        printf("\n");
        if (( result = PQgetResult(connection)) != NULL) {
            if (PQresultStatus (result)  == PGRES_TUPLES_OK) {
                print_result_set(result);
            } else if (PQresultStatus (result ) == PGRES_COMMAND_OK) {
                printf ("%s", PQcmdStatus(result));
                if (strlen(PQcmdTuples(result))) {
                    printf(" - %s rows\t", PQcmdTuples (result));
                } else {
                    printf ("\n");
                }
            } else {
                printf ("%s\n", PQresultErrorMessage(result));
            }
            PQclear(result);
        }
    } while (result != NULL);
}

void usage(char **argv) {
    printf("Usage: %s \"connection string\n\"", argv[0]);
    printf("       e.g.: %s \"hostaddr=10.0.0.25 dbname=apedevdb user=apedev password=vedepa port=5432\"\n", argv[0]);
    exit(1);
}

int main(int argc, char **argv) {
    PGconn *connection;

    if (argc != 2 ) {
        usage(argv);
    }
    if (( connection =  PQconnectdb(argv[1])) == NULL)  {
        printf("Unable to allocate connection\n");
        exit(1);
    }
    if (PQstatus(connection) != CONNECTION_OK) {
        printf("%s\n", PQerrorMessage(connection));
        exit(1);
    }
    process_query(connection);
    PQfinish(connection);

    return 0;
}

Please note the #define OUTPUT_AS_BINARY 1 flag; It took me some time.

file: 2_compile_and_run.sh

#!/bin/bash

gcc -o ./tmp/pg_0 \
    main.c \
    -I./tmp/postgresql-9.6.2/src/include \
    -I./tmp/postgresql-9.6.2/src/interfaces/libpq \
    -L./tmp/postgresql-9.6.2/src/interfaces/libpq \
    -lpq
./tmp/pg_1
gcc -o ./tmp/pg_2 \
    -D PARAMS=2 \
    main.c \
    -I./tmp/postgresql-9.6.2/src/include \
    -I./tmp/postgresql-9.6.2/src/interfaces/libpq \
    -L./tmp/postgresql-9.6.2/src/interfaces/libpq \
    -lpq
./tmp/pg_2

Further reading:

We only tipped the top of the iceberg here. There is much more:

Postgresql is amazing.

https://www.postgresql.org/docs/9.6/static/index.html