pqt-composites

NAME

pqt-composites - A manual for libpqtypes composite handling.

NOTE TO READER

Please read the pqt-specs(3) manual page prior to this document. This document does not explain how to put or get data types. It only describes how to put or get composites and composite arrays.

DESCRIPTION

A composite is put using the PGparam structure. Each attribute of a composite is put into a PGparam. When all attributes have been put, the PGparam is put into another PGparam. Composites must be registered on a per connection basis, `man pqt-handlers(3)´.

To get a composite, a PGresult structure is used. Each composite attribute is a field of the result. For non-array composites, there is always only one tuple.

Composites are only handled using binary format. This means that any type used as a composite attribute must be put and gotten in binary format. If a user-defined type does not implement a send and recv function in the backend, it can not be used as a composite attribute.

Simple Composite Example

This example demostrates the basics of putting and getting a composite type.

CREATE TYPE simple AS (a int4, t text);

PGregisterType type = {"simple", NULL, NULL};

/* need to register the simple composite */
PQregisterTypes(conn, PQT_COMPOSITE, &type, 1, 0);

/* Composite attributes are put into PGparam structures */
PGparam *simple = PQparamCreate(conn);

/* put the simple composite attributes */
PQputf(simple, "%int4 %text*", 45, "foobar");

/* Put an int4 and a simple composite */
PGparam *param = PQparamCreate(conn);
PQputf(param, "%int4 %simple", 10, simple);
PQparamClear(simple);

/* exec an insert */
res = PQparamExec(conn, param, "INSERT INTO t VALUES($1,$2)", resfmt);
PQparamClear(param);

/* -------------------------
* Getting a composite
*/

PGint4 i4;
PGtext textp;
char text[80];
PGresult *simple;

/* Get a simple composite, provide a ptr to a PGresult ptr. */
PQgetf(result, 0, "%simple", 0, &simple);

/* no longer needed */
PQclear(result);

/* 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 attributes.
*/
PQgetf(simple, 0, "#int4 #text", "a", &i4, "t", &textp);
strcpy(text, textp);
PQclear(simple);

In the above example, we used the ´#´ specifier mark to reference
fields by their name. The field names for a composite result object
are the composite attribute names.

Nested Composite example:

The below example puts and gets a nested composite. The simple composite is used as an attribute within the complex composite.

CREATE TYPE simple AS (a int4, t text)
CREATE TYPE complex AS (f8 float8, s simple);

/* need to register simple and complex */
PGregisterType types[] = {

		{"simple", NULL, NULL},
		{"complex", NULL, NULL}

};

PQregisterTypes(conn, PQT_COMPOSITE, types, 2, 0);

/* Composite attributes are put into PGparam structures */
PGparam *simple = PQparamCreate(conn);
PGparam *complex = PQparamCreate(conn);

/* put the simple composite attributes */
PQputf(simple, "%int4 %text*", 45, "foobar");

/* put the complex composite attributes, which includes
* a nested composite.
*/
PQputf(complex, "%float8 %simple", 111.2223334, simple);

/* no longer needed */
PQparamClear(simple);

/* Put an int4 and a complex composite */
PGparam *param = PQparamCreate(conn);
PQputf(param, "%int4 %complex", 10, complex);
PQparamClear(complex);

/* exec an insert */
res = PQparamExec(conn, param, "INSERT INTO t VALUES($1,$2)", resfmt);
PQparamClear(param);

/* -------------------------
* Getting a nested composite
*/

PGfloat8 f8;
PGint4 i4;
PGtext textp;
char text[80];
PGresult *complex;
PGresult *simple;

/* Get the complex composite, provide a ptr to a PGresult ptr. */
PQgetf(result, 0, "%complex", 0, &complex);

/* no longer needed */
PQclear(result);

/* Get the complex composite attributes from the complex result.
* Composite attributes are the result fields. When getting
* a single composite, non-array, only tuple 0 will exist.
* For the nested simple composite, we again provide a ptr to
* a PGresult ptr.
*/
PQgetf(complex, 0, "%float8 %simple", 0, &f8, 1, &simple);

/* no longer needed */
PQclear(complex);

/* Get the simple composite attributes from the simple result.
* Reference fields by name by using a ’#’ rather than a ’%’.
*/
PQgetf(simple, 0, "#int4 #text", "a", &i4, "t", &textp);
strcpy(text, textp);
PQclear(simple);

An array of composites:

This example makes an array of complex composites. It builds off the previous example.

int i;
PGarray complex_arr;
PGparam *simple = PQparamCreate(conn);
PGparam *complex = PQparamCreate(conn);

complex_arr.ndims = 0;
complex_arr.param = PQparamCreate(conn);

for(i=0; i < 100; i++)
{
/* put the simple composite attributes */
PQputf(simple, "%int4 %text*", 45, "foobar");

/* put the complex composite attributes, which includes
* a nested composite.
*/
PQputf(complex, "%float8 %simple", 111.2223334, simple);

/* put the complex composite */
PQputf(complex_arr.param, "%complex", complex);

/* You must reset the simple and complex composites for
* the next loop iteration.
*/
PQparamReset(simple);
PQparamReset(complex);
}

/* not needed anymore */
PQparamClear(simple);
PQparamClear(complex);

/* Put a complex composite array */
PGparam *param = PQparamCreate(conn);
PQputf(param, "%complex[]", &complex_arr);
PQparamClear(complex_arr.param);

/* exec an insert */
res = PQparamExec(conn, param, "INSERT INTO t VALUES($1)", resfmt);
PQparamClear(param);

/* -------------------------
* Getting an array of composites
*/

int i;
int ntups;
PGfloat8 f8;
PGint4 i4;
PGtext textp;
PGresult *simple;
PGarray complex_arr;

/* Get the complex[], provide a ptr to a PGarray. */
PQgetf(exec_result, 0, "%complex[]", 0, &complex_arr);

/* no longer needed */
PQclear(exec_result);

ntups = PQntuples(complex_arr.res);
for(i=0; i < ntups; i++)
{
PQgetf(complex_arr.res, i, "%float8 %simple", 0, &f8, 1, &simple);

/* Nested composites are like any other composite, tuple 0! Unless,
* its a nested composite array.
*/
PQgetf(simple, 0, "#int4 #text", "a", &i4, "t", &textp);

printf("(%f, (%d, %s))\n", f8, i4, textp);
PQclear(simple);
}

PQclear(complex_arr.res);

EXAMPLES

None.

AUTHOR

A contribution of eSilo, LLC. for the PostgreSQL Database Management System. Written by Andrew Chernow and Merlin Moncure.

REPORTING BUGS

Report bugs to <libpqtypes@esilo.com>.

COPYRIGHT

Copyright (c) 2011 eSilo, LLC. All rights reserved.
This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

SEE ALSO

PQgetf(3), PQputf(3), PQputvf(3)