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
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
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 PQspecPrepares 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.
This example prepares a type spec and issues some PQputf calls.
if(!PQspecPrepare(conn, "prepared_spec", "%int4 %text", 0))
fprintf(stderr, "PQspecPrepare: %s0, PQgeterror());
/* 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 doesnt know what to execute.
PQexecf(conn, "@myfunc", 123, "text");
/* clearm all */