[Ruby-DBI-devel] Array support for DBD::Pg

[Oliver M. B. <ol...@go...>]

Hi!

Currently, DBD::Pg does not support PostgreSQL's native Array types as
described in
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=arrays.html

Attached are two patches that add support for
- reading of Arrays
- quote()ing of Arrays

I have submitted the reading-part before, but did not receive any response
at that time. Seems like nobody is using PostgreSQL's arrays, except me :-)
I'd really appreciate some kind of comment from the DBD::Pg maintainer.

Now let me explain the patches:

pg_array_quote.diff:

PostgreSQL-Arrays are surronded by single quotes, the array delimiters
 are{ and }, the elements separated with comma. The problem is, that inside
the array, different quotingrules apply than usual. E.g. a Strings and Dates
are quoted with double-quotes instead of the usual single-quotes. Additionally,
all ' and \ must be extra escaped because the array as a whole is handled as
String in SQL.

To cope with this, an extra private function quote_array_elements() is added
to quote array-elements. This method is called from quote(), when the object
to be quoted is an Array.

The patch is based on Pg.rb 1.27 . I just discovered that in 1.28, support for
PGconn.quote was added. But the quoting of Arrays of PGconn.quote is WRONG.
So slight modifications would be additionally needed to bypass PGconn.quote if
the value is an array and use my quoting mechanism instead. 

pg_array.read.diff:

The goal is to get an array of the respectable type returned by DBD::Pg instead
of Strings like "{1,2,3}" when selecting a column with an array-type.
For this I've improved load_type_map() to register array-types and the type of
their elements. Entries with an _ in front of them are actually arrays, according
to the PostgreSQL documentation.
If an retrieved column is an array, it is passed to the new function
convert_array(), which is a nasty beast that parses the string that comes
out of PostgreSQL in to (possibly nested) Ruby-Arrays.

--
	Oliver M. Bolzer
	ol...@go...

GPG (PGP) Fingerprint = 621B 52F6 2AC1 36DB 8761  018F 8786 87AD EF50 D1FF