Re: [HACKERS] libpq support for arrays and composites

Andrew Dunstan wrote:
>
> One of the areas where libpq seems to be severely lacking is in handling
> arrays and composites in query results. I'd like to set about rectifying
> that.
>
> Ideally this would mean that drivers using libpq could easily and
> reliably deliver such objects suitably structured in their particular
> languages (e.g. lists and hashes in Perl).
>
> One complicating factor I see is that there is no protocol level support
> for anything other than simple objects - each data value is simply a
> stream of bytes of a known length. We would therefore need some pretty
> robust processing to pick apart structured objects.
>
> We'll need a new API to handle such objects. I'm thinking of something
> like:
>
> PQarray * PQgetArray( const PGresult *res, int row_number, int
> column_number);
> int PQgetArrayNDims(PQarray * array);
> int PQgetArrayLower(PQarray * array, int dim);
> int PQgetArrayUpper(PQarray * array, int dim);
> int PQgetArrayElementLength(PQarray * array, int dim1, ...);
> bool PQgetArrayElementIsNull(PQarray * array, int dim1, ...);
> char * PQgetArrayElement(PQarray * array, int dim1, ...);
>
> PQcomposite * PQgetComposite(const PGresult *res, int row_number, int
> column_number);
> PQcomposite * PQgetArrayElementComposite(PQarray * array, int dim1, ...);
> int PQgetCompositeNFields(PQcomposite * composite);
> char * PQgetCompositeFName(PQcomposite * composite, int fnumber);
> int PQgetCompositeFNumber(PQcomposite * composite, char * fname);
> Oid PQgetCOmpositeFType(PQcomposite * composite, int fnumber);
> int PQgetCompositeFieldLength(PQcomposite * , int fnumber);
> bool PQgetCompositeFieldIsNull(PQcomposite * composite, int fnumber);
> char * PQgetCompositeField(PQcomposite * composite, int fnumber);
>
> Not sure if we need analogs for PQfformat, PQfmod or PQfsize - I suspect
> not, but right now I'm just thinking out loud.
>
> Thoughts? Is this worth doing?
>
> cheers
>
> andrew
>
>
>

libpqtypes already implemented this. It is a different approach but provides
the same functionality; with the inclusion of being able to handle every data
type. libpqtypes uses the PGresult API for composites and arrays, rather than
adding a new set of functions.

To support this, one must be able to convert all data types (unless you are only
supporting text results) because composites can be made up of any data type.

Simple arrays:
http://libpqtypes.esilo.com/man3/pqt-specs.html#array

Composite arrays:
http://libpqtypes.esilo.com/man3/pqt-composites.html

EXAMPLE OF GETTING A COMPOSITE:
(taken from http://libpqtypes.esilo.com/ home page)

/* Let's get a composite.
* CREATE TYPE simple AS (a int4, t text);
*/
PGint4 i4;
PGtext text;
PGresult *res, *simple;
int resultFormat = 1;

/* Your composites need to be registered */
PQregisterTypeHandler(conn, "simple", NULL, NULL);

/* 2nd arg, PGparam, can be NULL if there are no query params.
* Composites require binary results, so we can't use PQexec().
*/
res = PQparamExec(conn, NULL, "SELECT my_simple FROM t", resultFormat);
if(!res)
fprintf(stderr, "ERROR: %s\n", PQgeterror());

/* Get the simple composite, which is exposed as a PGresult. */
PQgetf(res, 0, "%simple", 0, &simple);
PQclear(res); /* no longer needed */

/* Get the simple composite attributes from the simple result.
* Reference fields by name by using a '#' rather than a '%'.
* The field names are the composite attribute names.
*/
PQgetf(simple, 0, "#int4 #text", "a", &i4, "t", &text);
PQclear(simple);

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers