Re: Solution for "array in a result query"
Hello,
> Great! I've been working on array support but never had time to finish
> it.
>
I checked the HEAD of the repository-trunk, but did not find any evedence
for array support. Did you ever commit the work you have done so far?
> > If there is any interrest, I would like to publish this workarround on
> the
> > wiki. Where shall I post this?
>
> Easiest thing, I think, is to open a bug ticket for it on pqxx.org.
>
Basically it works like this: (I am afraid, this might by to much a
hack for some customers)
First I create a pure C function which does nothing else than copying the
raw array-data into a Postgres bytea-object. This function must be part of
a Postgres shared object, which has to be loaded into the server-side-part
of Postgres, ie. SPI-functions.
Next in the Postgres-shell you have to load this shared object, register
the function with Postgres and create a cast using this function:
# CREATE FUNCTION my_cast(int[]) RETURNS bytea AS 'mylib.so', 'my_cast' \
LANGUAGE c IMMUTABLE STRICT;
# CREATE CAST(int[] AS bytea) WITH FUNCTION my_cast(int[]);
During a pqxx-query, one can ask for the array as
std::ostringstream sql;
sql<<"SELECT CAST(myarray AS bytea) FROM mytable WHERE 1";
pqxx::result query = cur.exec(sql.str());
(...snip...)
not the raw array can be used as
pqxx::binarystring bytea(tuple[0]);
and by reintrerpret_cast'ing bytea.data() to my special class (which
basically only recalculates the offsets), the real integer array can be
extracted to an int*
This also works for float's and all other fixed-size types.
1. This solution has a big drawback. Since clients and servers can be
on different endian'ess hosts, array-data would have to be converted,
using function such as htonl and ntohl. In my case I can skip this, because
I know that they run on the same hardware.
2. Many clients might not be willing to install a shared module to do
the conversion between int[] and bytea.
While my solution works fine for me, I am not sure, that this can be a
general approach. However, I did not find any other solution to transfer
array data, unless one is willing to perform a
binary-to-ascii-to-binray-conversion.
If there is a better concept, please let me know. If you think my solution
is of generic usage, I will work on my conversion-class to make it more
pqxx-like.
Jacob
--
--
GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
Jeroen
RSS Feed