From octave-sources-request at bevo dot che dot wisc dot edu  Mon Aug 23 20:03:16 1999
Subject: octave-pg: Access PostgreSQL directly from Octave
From: Dirk Eddelbuettel <edd at debian dot org>
To: octave-sources at bevo dot che dot wisc dot edu
Date: Mon, 23 Aug 1999 21:03:12 -0400 (EDT)


--kWb2fvAev5
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


[ The README below is an ascii copy of the web page at 
	http://rosebud.sps.queensu.ca/~edd/code/octave-pg/html
  and the tar.gz is attached.  -edd ]


--kWb2fvAev5
Content-Type: application/octet-stream
Content-Description: octave-pg.tar.gz
Content-Disposition: attachment;
	filename="octave-pg.tar.gz"
Content-Transfer-Encoding: base64

H4sIAKLqwTcAA+1abVPbSBLO1+VX9HJ7h32RhQ2EJJDsHQkkoSoBFpNNUkBlZWlsK8iSo5Fw
vC/3y+/DPd2jkWzC2+5VdmvvNFtZwJ7p6ddnuntmPIjzUeBlnuv7d77QaHfa7bW1tTvtdrtz
/1579ieP+6vte3fa6/fXMauztraO+Z211ZU71P5SDM2OXGdeSnRHBcG18276/k86FpaXaWx9
gFoton0/884V9fPYz8IkpiyhVGVpqPAh5qk09L2IZHo/TUZ0kOhskCr9MQItJvc0GU/TcDDM
qPG0SZ2HDx/Sdpie0U4QqKiXqyxTET2CPv8ZqF7oxW6SDr7lhTz459Ew1DROk0HqjQi/9lOl
SCf9bOKlapOmSU6+F4OrINRgrJdnisKMvDhYTlIaJUHYnzIdfJbHgUopGyrKVDrSlPTlj+d7
r+m5ilUKSQ7yXhT69DL0VawVediaP9FDFVBP6PCKZ8xDt+CBniUg7LF6NkmF+D6lc5VqVteK
3aMg6FCSMpGGlzHnKSVjXtcEu1OKvKxa6vK0y+SvxAwojIX4MBlDpCFoQshJGEXUU5Rr1c8j
h0lgMr3ZPXqx//qItvbe0Zutw8OtvaN3m5icDRN8C3MaUuFoHIWgDMFSL86m4J8pvNo5fPoC
S7ae7L7cPXoHKejZ7tHeTrdLz/YPaYsOtg6Pdp++frl1SAevDw/2uzsuUVcxW4oJXKPjvpgJ
egxU5oWRLiV/B9NqsBcFNGQnTJWvwnMw55EPr7rZfEzEi5J4IHJicqXJTQr7FCeZQ5M0hMfA
rz8zLC+vbOvQbuy7Dt17SEcKWlJ0EHm+ohZ1c6awutp26Ancn6e+2qL2SqfTaXVW2/cdet3d
cot4+GY32KhiDDjrnFPH7UhkLLcfLK+sUOfBxur6Bn5BVNDOpzF9s7DwlzD2ozxQ9MhP4n44
cIfffvUVyAWqH8ZK2zg1X+apcDyzKPGzVtL7cHFVIqven3tRrt7Dy7OZJZiTx60gCi4uYj2V
eDBUHmJqlr8o7I0/3r3rvjCrGBCep6r73ctNipUKNLUi/n5hYXvn2eu999svt6lR6sMhLx1o
h2L8gF86tECLb+lxpbAGBT2HtIqUn733k9EIcd48ifHfwuF1uPRD0FtCSITwhR/mVy+Z1UdD
1l6Uj2JtQokjG17RUyXgBfR+hAPiPX9W7eEZjchWoWZSmJ+nMfupRlxb04w8UPnkEm/UT6Io
mTAz+Mw/EwAzIRvwnrAiUCBjWiALlYv7FprUUKVreH4Wpho68uAmIIDPK7vo3B9a1mZ2Swzq
MLM9T6sNQ0ew1k8V9qoo8M4rmfoE4OZfm2TEAkn+rFyGPZaMQrFnD9DU4O8b33SaTsdZa9LP
P5dTeVycs+6s3DjnIeYsUbVl5MWD3BsobPwxWto8iRcEI6E9I+9ud5+2wXI3myJKYWKw/jEP
U1VYlo+MAqe1yj5fwhupT8BBP8yiqQXZj7lKpywuIGnC5oJOGc4LZ2ToLWaOYaiRpwHmbhjj
434YqcJiXQXbBg7PNG4B6xWUCx9Qn5Qv2I6djOE0NSbgVdGEvy3Z0mPl42hjqp9J0Jyx63z4
nCz2wh9PFh06Pllk4Ss9wTeWQAT6xLcnJ3MW+XzwahG7dBPjJQ4AFuBrgk57kdK3JJekfDb3
pkLwZPG0uWk1BhNGOmHQ5G0ENU/ixebCTwug+BmCsZvir03GHvkbp4TxXJJJmleFcSYQo6Eb
/uFGKh4gxBpNWQdl9cAMVuJLBHqcwer5WE5GWd6nhln+9WOC//4kSGcOFFJxkg+G1UpHHIZG
Smv4rCgh598ai0ew+tJQRePKPktyGoZxP1lsbsrcgncrFW9kPFo+xkGUTTHvF2aL8wIEedDj
0IZki0Ev9kbq8aII1VODMI55QgXJjDSxhK4snaUhZjHaabSbrvnc6BlaIhAcKFbiSLGeLKBU
FBCa/iiwFDpXUjBcCBKzDPgUGhC8Ft77ScLIbbW5yQEWW4AUrBx66d9FJWNPa/6kEg/0Dgbb
BWu0/aTRMLq5G/Sarv+eQaYJTqxJMcN9ahQCAHziBY0msXGNbXuef6awjV/OEHMDaH2YFqkW
ua4rJlNpmqSNxYoUc2U1RH3kOCoQF77SwqBkBL3MzJyjs6isOasXRgvRhCOq8IcKpwqYrtAk
iiQLt6J+zcLu4NujHKmM3j9rGIOVaoHgs8J8JxhleL+cccue5FtnMDw2eBaqKNAcVSwV8Oss
RigICBvQm4BvyyMf9mZ1bFYb1mR1GXX0+DG1JeK+KhLbIAyYMvsSJ9FyDv/jc9bLQzk2trhe
iFdyWNPbBnK5s4J9Zr0SAYC8pMnkLDqhSGX4E2YJBzGOAqbBkdwQecLHbeSbj1AehHfvNgWZ
kmRMCXyYDUNA0gIqPONg1rmDBPksIIsZrch9YHIfHp3hfyBHBa5qjROk34Denqvse4my0PkA
QF78a9QHBv/tLf6mD3B4Zs7Ii8iGqt8a+exZaNESvostOS4v9dEBUnE+9DgubPqMfChCNggV
YsLLhNOj77009FiIDcnpUY+pDXp69678hcAfw6NaBQBs0OLoDIjOB+Zcjkyt3eVcp8tFkrk8
LqvMIptcFHI7TGLhjy6i/8Rj5oj9Yg2gG/o/66uddtn/WV+5x/2f9U6n7v/8HsP0f2Z8gFqt
VtXwkcx/vtdT5BFIqJIio637PnXfp+77XNX3mYmtyxs/DzY6q/9PjR+rkMs6P8fdjtNdcZAN
n0oRa+fepgl0Aauu6f+4tOP5w6L5w5FmU0WmByBIYmWLGpMTScI38s7wcQ73NUiE4CucjY2R
plxVlXVk1RWq6kKX9soe0hx5bVsBRSLIjo8yNvaVEcBW/Diq4KJpwKtXsFVpm6LXsPPJY3et
ejzHe87BBUXOtwOkoOeyC1X8kMUW1fk4FBNwqlGlk3NyQjfU8vPlPJO7opwve36FAP+DZb1V
dF3X13V9Xdf/8XX9lUX0T0aQJcD4fG2+dLZk7wWErnV3Ux13G3GzCt6cM0dbOCN6foiXivma
LtTw8y0Bs325J69Lk4kuULbbCJtGc3OVvUQ/oBlHUNgCyhVdBN74A/9tuBYav1iVcM3/gYl1
DdfijLbwl1WzoGq1dk31z+x+uQ7AXCVStwC++Djc2dp+tfNl9+D6f/2a9x/ra0X9v4pZ/BYE
8+v3H7/PuCKtszeYSZBHSs5iTw5H+9rDHMm3Ggv7ALnzUE0WJCPtnFYkUOxpwOcYMIP6M0DR
BzBjnEBhkXpyQwZsCaioMxzJt5nKALkEBYkvJ6YUQC7tZnIGfZQSDRjs8SEJ+J1wwgwBJqk3
ZsACYqOYxi5MyAc0FfW0lt0YtkwSfLxyarTgojQsegkhcqSeAro7hhKTsDm4SeKLI9BeEfLZ
GHL1FUdTsGhKdr69lY1NhqiR/zAhOU/GfBlm+gOSgvC/I7kLBINIP5WcGLxp4InwEde5YHrE
UkH+EV8DH6/OaNmRW2kmhJljLD/cfvKqS43QhWjhIE4ky2P5UDYqrh7BcsypBrAYxbXIxqwK
jVHC2zYljU7SjO0XIXkyZT2f2Nl0bK5dYzpeK1RYclBeokfT1iiMAzZucbfL6pEme59JpSrI
fb4b504FmEn7XIHzkTNJ5rReXK/zvTiryNzfy7U6s8xnsc93pxdeCJged3nB7RGXWbB8o5zX
nLu+13KLO3drD/JM4hxnXZKbewEjurmDn5btmTJfgYHmyz5HKs2CkJopScv3B8YHirpOKp4Z
ca0OUMvdO50prqCGkfJirqBY0eJ8c9qYs4RwbnzcPuVyTAPF5/QEpo8Sj+0cswSZ9NIKoZmO
Dn9U2iw/Xj+1V9yynpnQ9kYDbLG/eew4oKqyoqmjlVtKVb5aUEZgmkhLqOxGmotmZuSc9aSk
vEpHAgAStBJG5hK6evHAHx7OXYkvygzUwLYCFsKOpVrdZHNqKUrfQ7J7aFtUPc754IetwlrG
UUwzgCOQk1dO5gpPkHv8VLACM/2hRJskxLarZ1mdjXjrcjNGvn861zwxLwVmn+dxamZ8MOkz
GevqJmcUZkZXeSEDg5HHNY3I4weFNQ1OSrJ20SBc2BnPMN0DU29ro0HmpuwkbFzRjuBuBDcj
btGL4FbETYfOfBeCmxDGfk8S2GZPOD2Qdz5cvdjmCxzR5uzm2YWAjqTGmIVvOTSNFlKocQyz
aJv9mkdAcbUEllXRzAMiEeJSI78r+tncHJFCQoKG41SeALn0wsA9tPNw9tDMdS5hDadFOBZ2
tk+GoNcUERB5eshukNJwOkbGD2pvxD1jPn1CdihE/uz7IYcJmeOxQDlk5MBdiMBtYyY+/yym
gEOpJ2afTDEduRw47rTtCWpF3i3kNI9XhG1+ieKWuigf6dgHNhdf1jQ8kZi91dYDtO7ecztN
eZmDVKKPUOQdWDjkdpkpkSaF9PY5jUDX5a9hTMBDkc6Ft1TQHB8VzLbRzezjqzAzqUORKoBS
aRnx/V/12Epw7MZnVjYYrnhhddXX8riKv7zwqkrkfmPb507hVYUzVA7tmWNo/pHRTBhfqlN+
EXR17N76ZdHitVTmHhSZ2MfHWwMvBHqGfWnbyvXCvOc48kXBQumX8GFxtAv+J2h6Fo6Na8gx
XDkZg4fx9Sd5GAU2rXJR3sqTMKO5d5YNaEeakVOZddyZS41tCoxJZX3s2LwFXOmwh4WCHSEO
5QiFt/hVcRvGFMvPiusT27cXVoTPfRxeU+y8crotV3KkpxB15JhAlRPrN9bmF9f+ipt9g9i7
xl7IPQKLCkJKoKXxtrkz8nw46dOWL/9MsgWtFzdjpk4weAafzBjdTU+P3v+3w6LZcWf1dOaG
899ywfkSzmLuIkMVbFA3j2krHxDf9axtrKxutNdpZ/tI7oAWFg5VHy4T+4VndFwaZtl4Y3l5
Mpm4lWL4pnSZJ6zMTfCHyp2E2neRKy8n5Vm9ehOVtdtQuVdOShOtenng6rF2gf04THLX95b/
hYp4mcuYYlFrPFietbObGRxbv81u93/bbjMeabd7cJvtHt6gok77FlQ6N5mrM2+u6tZbvly9
jch1X6se9ahHPepRj3rUox71qEc96lGPetSjHvWoRz3qUY961KMe9ajHrcd/ABl+1FgAUAAA

--kWb2fvAev5
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable




Octave modules to access PostgreSQL
                                      =20
Overview

   [1]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 [2]Octave. For the time being, these
   functions can execute select queries only. I will add code for inser=
t
   and update later.
  =20
   There is however a fundamental problem in as much [3]PostgreSQL, as =
a
   proper RDBMS (i.e. ignoring the object-oriented extensions for a
   moment) supports a lot more data types than [4]Octave as a
   numerically-minded language. I therefore reduced the interface to tw=
o
   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 fo=
r
   each column retrieved.
  =20
Examples

   The first function, [5]pgnumdata, is meant really only for strictly
   numerical data. For Postgres, this covers floats and ints of various=

   sizes. For [6]Octave, this means doubles, i.e. a superset of these.
   The following example would retrieve price and volume information fr=
om
   a sales database:
   R =3D 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.
  =20
   The second function, [7]pgtextdata, allows to retrieve all types of
   data and stores them in string vectors, one per column. This [8]Octa=
ve
   command would retrieve names and phone numbers from all customers:
   [N,P] =3D 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 databas=
e.
  =20
   You can also use this for dates. However, [9]PostgreSQL usually prin=
ts
   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 [10]Octave.
  =20
   I use the ISO datestyle. You can either select it postmaster.init (a=
s
   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 =3D 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.
  =20
Building the .oct files

   You have to supply the [11]PostgreSQL library to mkoctfile, and
   possibly also its location and/or the location of the include files.=

   On my [12]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.
     _________________________________________________________________
  =20
   [13]Dirk Eddelb=FCttel Last modified: Sun Aug 22 14:23:06 EDT 1999

References

   1. http://www.postgresql.org/
   2. http://www.che.wisc.edu/octave
   3. http://www.postgresql.org/
   4. http://www.che.wisc.edu/octave
   5. http://rosebud.sps.queensu.ca/~edd/code/octave-pg/pgnumdata.cc.tx=
t
   6. http://www.che.wisc.edu/octave
   7. http://rosebud.sps.queensu.ca/~edd/code/octave-pg/pgtextdata.cc.t=
xt
   8. http://www.che.wisc.edu/octave
   9. http://www.postgresql.org/
  10. http://www.che.wisc.edu/octave
  11. http://www.postgresql.org/
  12. http://www.debian.org/
  13. http://rosebud.sps.queensu.ca/~edd


--=20
According to the latest figures, 43% of all statistics are totally wort=
hless.

--kWb2fvAev5--