libpqtypes home page

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
       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 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)

libpqtypes home page