FreeBSD Manual Pages
pqt-composites(3) libpqtypes Manual pqt-composites(3) 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 de- scribes 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 at- tribute 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 compos- ite 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) libpqtypes 2011 pqt-composites(3)
NAME | NOTE TO READER | DESCRIPTION | EXAMPLES | AUTHOR | REPORTING BUGS | COPYRIGHT | SEE ALSO
Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=pqt-composites&sektion=3&manpath=FreeBSD+12.1-RELEASE+and+Ports>