Re: Proposal - libpq Type System beta-0.8a (was PGparam)

On Jan 8, 2008 4:31 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Merlin,
>
> > That said, we have been a little dismayed in the lack of comment.
>
> I think most people can't really follow what functionality this would allow
> users & driver authors to access, and what the spec for that functionality
> would. I know I'm not clear on it.
>
> A high-level proposal would arouse more general interest. Otherwise,
> you'll just get a critique and eventually it'll either get applied or
> rejected without much comment.

There are over 1500 lines of documentation attached in the patch.
That's a good place to start learning (or the attached regression
test, if you want to get right to it). There's a lot of material to
cover. Here's a very high level overview of the functionality:

Goal:
The ultimate goal is to be able to put parameters for command
execution and get results in a consistent way. The wire format
(either text or binary) is completely abstracted. We added many
functions to facilitate this, but the core functionality comes from
two varargs style functions, PQgetf and PQputf, and the PGparam, which
complements PGresult.

Features:
*) Binary transfer of all built in types is supported in both
directions. Basically, we marshal backend wire format to/from C types
(some native, some were introduced). We also support text results so
you can pull data in a consistent interface.
*) For user types (with custom send/recv functions), functions can be
registered to marshal them through a type registration interface.
*) Arrays and composites are supported automatically (composites have
to be 'registered').
*) Client side handlers can be aliased (domains) or subclassed...type
handlers can chained together for special handling and/or conversion
to exotic application types.

Here is a short example which demonstrates some of the major features.
There are many other examples and discussions of minutia in the
documentation.

int resfmt = 1; /* binary format */

/* Put an int4 and a text */
PGparam *param = PQparamCreate(conn);
PQputf(param, "%int4 %text", 2000, "foobar");
PQparamExec(conn, param, resfmt,
"insert into foo(id, user) values ($1, $2)");

/* The above as a one liner, internally 'puts' for you */
res = PQexecParamsf(conn, resfmt,
"insert into foo(id, user) values (%int4, %text)",
2000, "foobar");

int i4;
char *text;
PGresult *res = PQparamExec(conn, NULL, resfmt, "select * from foo limit 1");

/* From tuple 0, get an int4 at field 0 and a text
* from the user field
* '%' denotes by field num, '#' by field name
*/
PQgetf(res, 0, "%int4 #text*", 0, &i4, "user", &text);

note the above line is not wired to binary, text results would be fine as well.

/* let's get an array */

PGresult *res = PQparamExec(conn, NULL, resfmt, "select current_schemas(true)");

/* pop an array object out of the result. it creates a new result
* with one field and one 'tuple' for each array element.
*
* arrays of composites return one field for each attribute of the composite.
*/
PGarray array;
PQgetf(res, 0, "%name[]", 0, &array);
PQclear(res);

for (i = 0; i < PQntuples(array.res); i++)
{
char *name;
PQgetf(array.res, i, "%name*", 0, &name);
printf("%s\n", name);
}
PQclear(array.res);

/* return data from composite type which we create and register */
CREATE TYPE merlin as (a int, t text); -- on server
PGresult *merlin;
PQregisterTypeHandler(conn, "merlin", NULL, NULL);
res = PQparamExec(conn, NULL, resfmt, "select (1, 'abc')::merlin");
PQgetf(res, 0, "%merlin", 0, &merlin);
PQclear(res);
PQgetf(merlin, 0, "%int4 #text*", 0, &i4, "t", &text);
PQclear(merlin);

merlin