Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
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>

home | help