PostgreSQL has a pretty decent C++ library called libpq++, with good documentation. It was quite straightforward to wrap these in C++ code that is callable from Octave. For the time being, these functions can execute select queries only. I will add code for insert and update later.

There is however a fundamental problem in as much PostgreSQL, as a proper RDBMS (i.e. ignoring the object-oriented extensions for a moment) supports a lot more data types than Octave as a numerically-minded language. I therefore reduced the interface to two functions. The first is to be used for strictly numerical data and returns a single (numerical) matrix. The second can be used for various datatypes as they will be returned in string vectors, one for each column retrieved.


The first function, pgnumdata, is meant really only for strictly numerical data. For Postgres, this covers floats and ints of various sizes. For Octave, this means doubles, i.e. a superset of these. The following example would retrieve price and volume information from a sales database:

   R = pgnumdata("sales", "select price, volume from sales");

Now R should be a two-column matrix with as many rows as there were matching tuples in the database.

The second function, pgtextdata, allows to retrieve all types of data and stores them in string vectors, one per column. This Octave command would retrieve names and phone numbers from all customers:

   [N,P] = pgtextdata("biz", ["select name, phone from customers " ,\
                               "order by name"]);

Both N and P are now vectors of strings where the i-th row of each corresponds to the data in the i-th tupel retrieved from the database.

You can also use this for dates. However, PostgreSQL usually prints dates with extra slashes or hyphens. With an additional SQL function, these can be filtered out within the query and can then be retrieved into Octave.

I use the ISO datestyle. You can either select it postmaster.init (as per postgres 6.5.1), or prefix the SQL statement with an explicit

   set DateStyle to 'ISO';

Next, the following adds a datefilter function suitable for the ISO dates:

   create function date2text (date) returns text
   as 'select substr(text($1),1,4) ||
       substr(text($1),6,2) ||
   language 'sql';

With this, dates can be retrieved as

   X = pgnumdata("biz", ["set DateStyle to 'ISO';", \
                         "select date2text(date), price from sales" \
                         "order by date"]);

Again, if you have postgres 6.5.1, you select ISO dates in postmaster.init and skip the first statement here.

Building the .oct files

You have to supply the PostgreSQL library to mkoctfile, and possibly also its location and/or the location of the include files. On my Debian system, I use

   mkoctfile pgtextdata.cc -I/usr/include/postgresql -lpq++
   mkoctfile pgnumdata.cc -I/usr/include/postgresql -lpq++

If you load either file into (X)Emacs, C-c C-c will built it with these settings.

Last modified: Wed Aug 9 00:27:05 EDT 2000