NAME¶
PQspecPrepare - Prepares a libpqtypes specifier format string.
SYNOPSIS¶
#include <libpqtypes.h>
int PQspecPrepare(PGconn *
conn, const char *
name,
const char *
format, int
is_stmt);
void PQclearSpecs(PGconn *
conn);
DESCRIPTION¶
PQspecPrepare allows an application to prepare specifier format strings that
will be used frequently. By preparing a specifier format string, one avoids
the parsing and type handler lookup costs. This becomes a significant win when
managing large result sets or arrays, where the specifier format, like
"%int4 %text %bytea", must be prepared for each tuple/element.
As with PQregisterXXX, only specifier format strings prepared prior to the
creation of a PGresult or PGparam, will be available for use. This is because
the prepared type spec is cached within a PGconn object and copied to all
subsequent PGparam and PGresult objects.
Every prepared type spec is given a
name, which is used as its unique
identifier. To use a prepared type spec, the
name is provided where
ever a regular specifier format string is allowed, like PQputf or PQgetf. The
name must be proceeded by a ´@´ AT sign. For more
information about the syntax, see the
pqt-specs(3) man page.
The
format argument is the specifier format string being prepared. When
this is NULL, the
name prepared type spec is removed from the
PGconn´s internal array.
The
is_stmt argument indicates if a parementerized statement version of
format should be cached along with the prepared type spec. This means
all type specifiers in
format, like "%int4", will be
converted to "$1" syntax. When is_stmt is non-zero, a statement will
created and cached. For more information on specifer format string to
paremterized statements, see the
PQputf(3) man page. NOTE: to use a
prepared type spec with execution functions like PQexecf,
is_stmt must
be set to non-zero.
PQclearSpecs removes all prepared specifiers from the given PGconn, as opposed
to removing them one by one by setting PQspecPrepare's format argument to
NULL. A good use for this is after a PQresetXXX call when it might be desired
to re-prepare all type specifiers.
Functions that support the use of a prepared type spec are:
PQputf,
PQputvf,
PQgetf,
PQgetvf,
PQexecf,
PQexecvf,
PQsendf,
PQsendvf,
PQparamExec,
PQparamSendQuery.
HINT: A good rule of thumb for using prepared type specs, is when there
are a large number of PQputf/PQgetf calls per statement execution. This
commonly occurs when dealing with large result sets and arrays.
RETURN VALUE¶
PQspecPrepare and PQclearSpecs return a nonzero value on success and zero if it
fails. Use PQgeterror() to get an error message.
EXAMPLES¶
This example prepares a type spec and issues some PQputf calls.
int i;
PQparam *param;
if(!PQspecPrepare(conn, "prepared_spec", "%int4 %text", 0))
{
fprintf(stderr, "PQspecPrepare: %s0, PQgeterror());
exit(1);
}
/* create after preparing spec */
param = PQparamCreate(conn);
for(i=0; i < 100000; i++)
{
/* NOTE: nothing else can be in format string */
PQputf(param, "@prepared_spec", 4, "text");
}
/* This elects to prepare a statement as well. After this returns,
* "SELECT myfunc($1, $2)" will be cached along with the prepared spec.
*/
PQspecPrepare(conn, "myfunc", "SELECT myfunc(%int4, %text)", 1);
/* "myfunc" tells execf to execute "SELECT myfunc($1, $2)". If is_stmt
* was set to zero during the PQspecPrepare, the below would be invalid
* because execf doesn't know what to execute.
*/
PQexecf(conn, "@myfunc", 123, "text");
/* clear'm all */
PQclearSpecs(conn);
RATIONALE¶
None.
AUTHOR¶
A contribution of eSilo, LLC. for the PostgreSQL Database Management System.
Written by Andrew Chernow.
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¶
pqt-specs(3),
PQgetf(3),
PQputf(3).