#C _libpq_essentials.md

#C libpq essentials

Overview

• libpq is a C library that enables a programmer to connect with a PostgreSQL database to send queries and access related results.
• C code compiled with gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0
• Database instance running PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
• Test was performed on Ubuntu 20.04 using WSL2 for Windows10.
• Your credentials need to be added for the test file to produce the output section.

Reference

• General documentation
• Doxygen documentation

Test File / Usage

// C_libpq_essentials.c
// Connects with database, sends query, displays result.

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

void exit_nicely(PGconn *conn){
    fprintf(stderr, "%s\n", PQerrorMessage(conn));
    PQfinish(conn);
    exit(1);
}

int main(int argc, char **argv){
    
    // Open connection with database.
    char conninfo[100] = "host=cse180-db.lt.ucsc.edu user=";
    strcat(conninfo, "<YOUR USERNAME>");
    strcat(conninfo, " password=<YOUR PASSWORD>");
    PGconn *conn = PQconnectdb(conninfo);
    if(PQstatus(conn) != CONNECTION_OK){ exit_nicely(conn); }
    printf("Logged-in\n");
    
    // Send query, receive result then error check.
    PGresult* res = PQexec(conn, "SELECT VERSION();");
    if(PQresultStatus(res) != PGRES_TUPLES_OK){
        printf("ERROR%d: %s\n", PQresultStatus(res), PQresStatus(PQresultStatus(res)));
        PQclear(res); 
        exit_nicely(conn);
    }
    printf("Query successful\n");
    
    /* Useful to know but not used in example.
    int maxAttributes = PQnfields(res); 
    int maxRows = PQntuples(res);
    */
    
    // Extract data from result.
    int attribute_i = 0;
    int row_i = 0;
    printf("Attribute1 name: %-15s\n", PQfname(res, attribute_i));
    printf("Attribute1, row1 value: %-15s\n", PQgetvalue(res, attribute_i, row_i));
    PQclear(res); 
 
    // Close connection with database.
    PQfinish(conn);
    return 0;
}

Output

gcc C_libpq_essentials.c -I/usr/include/postgresql -lpq -o test
./test
Logged-in
Query successful
Attribute1 name: version        
Attribute1, row1 value: PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit