pqt-composites(3)

📦 version 1.5.1, in debian bookworm, ubuntu jammy, ubuntu noble

💖 pqt-composites(3) 💖

okay so bestie, this is the libpqtypes Manual page all about composite handling 🦋 and ngl it's kinda the moment. grab a snack, we're doing this. ✨

🌸 NAME 🌸

pqt-composites — the manual for libpqtypes composite handling. that's it, that's the name, she's iconic 💅

🎀 NOTE TO READER 🎀

bestie, please go read the pqt-specs(3) manual page before this one. fr fr. this doc does NOT explain how to put or get data types. it only describes how to put or get composites and composite arrays. that's the whole vibe. 😌

🦋 DESCRIPTION 🦋

so you put a composite using the PGparam structure. every attribute of that composite gets put into a PGparam. and then, once all the attributes are in there, the PGparam itself gets put into another PGparam. it's giving nesting doll energy, and we love that for it. 🪆

also — composites must be registered on a per connection basis, so go peek at man pqt-handlers(3). iykyk.

to get a composite, you use a PGresult structure. every composite attribute is a field of the result. for non-array composites, there is always only one tuple. always. one. no cap. 👑

and here's the part that matters most: composites are only handled using binary format. so any type you use as a composite attribute has to be put and gotten in binary format too. and 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. sorry bestie, that's the rule. 💀

✨ Simple Composite Example ✨

this one demonstrates the basics of putting and getting a composite type. baby's first composite, we're so proud 🥹

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 🌸 and the field names for a composite result object are the composite attribute names. slay.

💜 Nested Composite example: 💜

the below example puts and gets a nested composite ✨ the simple composite is used as an attribute within the complex composite. it's giving composite-ception and honestly? obsessed.

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, and it builds off the previous example. glow up fr. 💅

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. 💀 literally none, bestie. the examples above WERE the examples. we ate and left no crumbs.

👑 AUTHOR 👑

a contribution of eSilo, LLC. for the PostgreSQL Database Management System. written by Andrew Chernow and Merlin Moncure. legends, honestly. 🫶

🧚 REPORTING BUGS 🧚

found a bug? not the vibe, but okay — report bugs to libpqtypes@esilo.com. 🙈

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. so like, no promises, bestie. 😭

💫 SEE ALSO 💫

go read those too, they're the supporting cast and they serve. 💜

and that's the page!! libpqtypes, dated 2011, manual for pqt-composites(3) — tysm for reading, ily, go write some composites 🎀✨🦋💖

📦 where this page comes from