PQsendQuery+PQgetResult+PQsetSingleRowMode limitations and support

Hi,

Bruce Momjian suggested I write and ask about using libpq
to submit multiple SQL statements to the backend, and
then get results for each of the submitted statements,
row-by-row without server-side caching of the results.

Bruce wrote:
> I think this would be good
> to post to hackers to get a general discussion of the limitations of
> this approach and allow to communicate if this is something we want
> those interfaces to support. My guess is this never used to work, but
> now it does.

As I read the documentation this functionality is supported.
(Although I do believe that the wording could be more clear.)

http://www.postgresql.org/docs/9.5/static/libpq-single-row-mode.html

And (I suppose):
http://www.postgresql.org/docs/9.5/static/libpq-async.html

FWIW, I would use such functionality to support an
interactive interface for users wishing to write SQL
and query the db directly. Like psql does, only not
from the command line.

The following example program exhibits this functionality.
It runs on Debian Jesse (8.3) postgresql 9.4 (from the
Debian repos).

----------------------------<snip>------------------
/*
* byrow.c
*
* Test that libpq, the PostgreSQL frontend library, can be given
* multiple statements and get the results of executing each,
* row-by-row without server side buffering.
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int stmtcnt = 0;

static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}

static void
setting_failed(PGconn *conn)
{
fprintf(stderr, "Unable to enter single row mode: %s\n",
PQerrorMessage(conn));
exit_nicely(conn);
}

int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
int first = 1;
int nFields;
int i,
j;

/* Construct some statements to execute. */
char *stmts = "select * from pg_database;\n"
"select * from pg_roles;\n"
"select count(*) from pg_tables;\n";

/*
* If the user supplies a parameter on the command line, use it as
* the conninfo string; otherwise default to setting
* dbname=postgres and using environment variables or defaults for
* all other connection parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname = postgres";

/* Make a connection to the database */
conn = PQconnectdb(conninfo);

/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}

/* Send our statements off to the server. */
if (!PQsendQuery(conn, stmts))
{
fprintf(stderr, "Sending statements to server failed: %s\n",
PQerrorMessage(conn));
exit_nicely(conn);
}

/* We want results row-by-row. */
if (!PQsetSingleRowMode(conn))
{
setting_failed(conn);
}

/* Loop through the results of our statements. */
while (res = PQgetResult(conn))
{

switch (PQresultStatus(res))
{
case PGRES_TUPLES_OK: /* No more rows from current query. */
{
/* We want the next statement's results row-by-row also. */
if (!PQsetSingleRowMode(conn))
{
PQclear(res);
setting_failed(conn);
}
first = 1;
break;
}

case PGRES_SINGLE_TUPLE:
{
if (first)
{
/* Produce a "nice" header" */
printf("\n%s\nResults of statement number %d:\n\n",
"-----------------------------"
"-----------------------------",
stmtcnt++);

/* print out the attribute names */
nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-15s", PQfname(res, i));
printf("\n\n");

first = 0;
}

/* print out the row */
for (j = 0; j < nFields; j++)
printf("%-15s", PQgetvalue(res, 0, j));
printf("\n");

break;
}
default:
/* Always call PQgetResult until it returns null, even on
* error. */
{
fprintf(stderr,
"Query execution failed: %s", PQerrorMessage(conn));
}
}

PQclear(res);
}

/* close the connection to the database and cleanup */
PQfinish(conn);

return 0;
}
----------------------------<snip>------------------

(You may recognize much of the code above because it was cribbed
from the libpq docs example #1.)

I assume there are no questions about supporting a
similar functionality only without PQsetSingleRowMode,
as follows:
----------------------------<snip>------------------
/*
* testmultistmt.c
*
* Test that libpq, the PostgreSQL frontend library, can be given
* multiple statements and get the results of executing each.
* (Not just results from the last statement executed.)
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}

int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
int stmtcnt = 0;
int nFields;
int i,
j;

/* Construct some statements to execute. */
char *stmts = "select * from pg_database;\n"
"select * from pg_roles;\n"
"select count(*) from pg_tables;\n";

/*
* If the user supplies a parameter on the command line, use it as
* the conninfo string; otherwise default to setting dbname=postgres
* and using environment variables or defaults for all other
* connection parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname = postgres";

/* Make a connection to the database */
conn = PQconnectdb(conninfo);

/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}

/* Send our statements off to the server. */
if (!PQsendQuery(conn, stmts))
{
fprintf(stderr, "Sending statements to server failed: %s\n",
PQerrorMessage(conn));
exit_nicely(conn);
}

/* Loop through the results of our statements. */
while (res = PQgetResult(conn))
{
/* Produce a "nice" header" */
printf("\n%s\nResults of statement number %d:\n\n",
"------------------------------------------------------",
stmtcnt++);

if (PQresultStatus(res) == PGRES_TUPLES_OK)
{

/* first, print out the attribute names */
nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-15s", PQfname(res, i));
printf("\n\n");

/* next, print out the rows */
for (i = 0; i < PQntuples(res); i++)
{
for (j = 0; j < nFields; j++)
printf("%-15s", PQgetvalue(res, i, j));
printf("\n");
}

}
else
/* Always call PQgetResult until it returns null, even on
* error. */
{
fprintf(stderr,
"Query execution failed: %s", PQerrorMessage(conn));
}

PQclear(res);
}

/* close the connection to the database and cleanup */
PQfinish(conn);

return 0;
}
----------------------------<snip>------------------

Regards,

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein