||Octave modules to access Postgres|
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.
[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) || substr(text($1),9,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
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.