Exporting PostgreSQL Data in C

In previous posts I have covered Creating a PostgreSQL Database in C and PostgreSQL Data Manipulation in C. In this post I will write and demonstrate a few simple functions which use the libpq library's PQprint function to export data to the terminal, HTML and CSV files.

The Project

This project builds on the previous ones so assumes you have read them and run the code, and still have the database.

The source code for this project consists of the following files:

cdpgconnection.h
cdpgconnection.c
cdpgfileexport.h
cdpgfileexport.c
fileexportdemo.c

which can be downloaded in a ZIP or cloned/downloaded from Github. This project shares cdpgconnection.h and cdpgconnection.c with the previous post so you might like to save the files for this post in the same folder and share the previous files.

Source Code Links

ZIP File
GitHub

The PQprint Function

The libpq library provides a useful and flexible function called PQprint to print the data in a PGresult to a file (which can be stdio of course) with various formatting options. The full documentation can be found here:

libpg Command Execution Functions

The PQprint function is defined as

PQprint

void PQprint(FILE *fout, const PGresult *res, const PQprintOpt *po);

The first argument is a file to print to, and the second is a PGresult containing data to print. The third is a PQprintOpt struct which has ten members specifying the print format. This is the definition of the PQprintOpt struct, pirated from the official documentation.

PQprintOpt

typedef struct
{
    pqbool  header;      /* print output field headings and row count */
    pqbool  align;       /* fill align the fields */
    pqbool  standard;    /* old brain dead format */
    pqbool  html3;       /* output HTML tables */
    pqbool  expanded;    /* expand tables */
    pqbool  pager;       /* use pager for output if needed */
    char    *fieldSep;   /* field separator */
    char    *tableOpt;   /* attributes for HTML table element */
    char    *caption;    /* HTML table caption */
    char    **fieldName; /* null-terminated array of replacement field names */
} PQprintOpt;

Apart from standard which is described as "old brain dead format" (which I assume means it is obsolete) I will be using various combinations of these in my code, but you can easily experiment with others to meet your exact requirements.

Opening and Closing a Database Connection

The cdpgconnection.h and cdpgconnection.c files prototype and implement functions to open and close a database connection. They were described in the first post in this series, linked to above, so I will not go over them again.

The Export Functions

Firstly let's take a look at the function prototypes in cdpgfileexport.h.

cdpgfileexport.h

#include<libpq-fe.h>

void result_to_terminal(PGresult* result);
void result_to_html(PGresult* result, char* filepath);
void result_to_csv(PGresult* result, char* filepath);

The result_to_terminal function just needs a PGresult, whereas result_to_html and result_to_csv also need filepaths. They are implemented in cdpgfileexport.c.

cdpgfileexport.c

#include<stdbool.h>

#include<libpq-fe.h>

void result_to_terminal(PGresult* result)
{
    if (PQresultStatus(result) != PGRES_TUPLES_OK)
    {
        printf("No data\n");
    }
    else
    {
        PQprintOpt pqpo = (PQprintOpt){.header = true,
                                       .align = true,
                                       .standard = false,
                                       .html3 = false,
                                       .expanded = false,
                                       .pager = 0,
                                       .fieldSep = "|",
                                       .tableOpt = "",
                                       .caption = "",
                                       .fieldName = NULL};

        PQprint(stdout, result, &pqpo);
    }
}

void result_to_html(PGresult* result, char* filepath)
{
    if (PQresultStatus(result) != PGRES_TUPLES_OK)
    {
        puts("No data\n");
    }
    else
    {
        FILE* fp;

        fp = fopen(filepath, "w");

        if(fp != NULL)
        {
            PQprintOpt pqpo = (PQprintOpt){.header = true,
                                           .align = false,
                                           .standard = false,
                                           .html3 = true,
                                           .expanded = false,
                                           .pager = 0,
                                           .fieldSep = "",
                                           .tableOpt = "",
                                           .caption = "",
                                           .fieldName = NULL};

            PQprint(fp, result, &pqpo);

            fclose(fp);
        }
        else
        {
            printf("Cannot open %s for writing", filepath);
        }
    }
}

void result_to_csv(PGresult* result, char* filepath)
{
    if (PQresultStatus(result) != PGRES_TUPLES_OK)
    {
        printf("No data\n");
    }
    else
    {
        FILE* fp;

        fp = fopen(filepath, "w");

        if(fp != NULL)
        {
            PQprintOpt pqpo = (PQprintOpt){.header = false,
                                           .align = false,
                                           .standard = false,
                                           .html3 = false,
                                           .expanded = false,
                                           .pager = 0,
                                           .fieldSep = "|",
                                           .tableOpt = "",
                                           .caption = "",
                                           .fieldName = NULL};

            PQprint(fp, result, &pqpo);

            fclose(fp);
        }
        else
        {
            printf("Cannot open %s for writing", filepath);
        }
    }
}

Each of these three functions checks PQresultStatus and prints a message if there is no data. In result_to_terminal we just need to create a PQprintOpt and pass it to PQprint along with a FILE pointer and the PGresult. In result_to_html and result_to_csv we do the same but also need to open and check a FILE pointer using the specified path.

You might like to spend a few minutes looking over the various PQprintOpt values, and perhaps trying a few different ones. Note that for the CSV file I have used "|" as a field separator. Most people tend to think of CSV as standing for "comma separated values" but I prefer to think of it as "character separated values" and usually use "|" instead of "," as textual data can often contain commas, thereby completely messing up the format.

Now we just need a short bit of code in a main function to try out our export functions.

The main Function

fileexportdemo.c

#include<stdio.h>
#include<stdlib.h>
#include<stdbool.h>

#include"cdpgconnection.h"
#include"cdpgfileexport.h"

int main(int argc, char* argv[])
{
    puts("--------------------------");
    puts("| codedrome.com          |");
    puts("| PostgreSQL with libpq  |");
    puts("| File Exports           |");
    puts("--------------------------\n");

    PGconn* conn = CDPGget_connection("user=user password=password dbname=codedrome");

    if(conn != NULL)
    {
        char* sql = "SELECT galleryname, gallerydescription, phototitle, photodescription FROM public.galleriesphotos";

        PGresult* res = PQexec(conn, sql);

        result_to_terminal(res);

        result_to_html(res, "galleriesphotos.html");

        result_to_csv(res, "galleriesphotos.csv");

        PQclear(res);

        CDPGclose_connection(conn);
    }

    return EXIT_SUCCESS;
}

I have already written about querying data with libpq so won't go over it again. I have used the same techniques as in my previous post to obtain a PGresult, which is then passed to the three export functions.

Compiling and Running

Now we can compile and run the program with these commands.

Compile and run

gcc fileexportdemo.c cdpgconnection.c cdpgfileexport.c -std=c11 -I/usr/include/postgresql -lpq -o fileexportdemo
./fileexportdemo

You will see the output from result_to_terminal on screen, and two files will also be created in your project folder, galleriesphotos.html and galleriesphotos.csv. All three are shown below.

Program Outputs

The first output below is what you will see in the terminal. The second and third are the contents of the HTML and CSV files respectively. The HTML file contains only a table and is not a full HTML document. However, it can still be displayed by a browser although it will look pretty ugly without some suitable CSS to tidy it up.

Program output - terminal

Program output - HTML

<table><caption align="top"></caption>
<tr><th align="right">galleryname</th><th align="right">gallerydescription</th><th align="right">phototitle</th><th align="right">photodescription</th></tr>
<tr><td align="right">Edinburgh</td><td align="right">Photos of Edinburgh</td><td align="right">Edinburgh 1</td><td align="right">First photo of Edinburgh</td></tr>
<tr><td align="right">Canterbury</td><td align="right">Photos of Canterbury</td><td align="right">Canterbury 1</td><td align="right">First photo of Canterbury</td></tr>
<tr><td align="right">Canterbury</td><td align="right">Photos of Canterbury</td><td align="right">Canterbury 2</td><td align="right">Second photo of Canterbury</td></tr>
<tr><td align="right">Cambridge</td><td align="right">Photos of Cambridge</td><td align="right">Cambridge 1</td><td align="right">First photo of Cambridge</td></tr>
<tr><td align="right">Cambridge</td><td align="right">Photos of Cambridge</td><td align="right">Cambridge 2</td><td align="right">Second photo of Cambridge</td></tr>
<tr><td align="right">Jersey</td><td align="right">Photos of Jersey</td><td align="right"></td><td align="right"></td></tr>
</table>

Program output - CSV

Edinburgh|Photos of Edinburgh|Edinburgh 1|First photo of Edinburgh
Canterbury|Photos of Canterbury|Canterbury 1|First photo of Canterbury
Canterbury|Photos of Canterbury|Canterbury 2|Second photo of Canterbury
Cambridge|Photos of Cambridge|Cambridge 1|First photo of Cambridge
Cambridge|Photos of Cambridge|Cambridge 2|Second photo of Cambridge
Jersey|Photos of Jersey||

CodeDrome

Practical and useful coding projects in JavaScript, Python and C.

The Project

The PQprint Function

Opening and Closing a Database Connection

The Export Functions

The main Function

Compiling and Running

Program Outputs

Leave a Reply Cancel reply