PQsendQuery+PQgetResult+PQsetSingleRowMode limitations and support - Mailing list pgsql-hackers

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
resultsof 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
statementsto 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
getthe 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
settingdbname=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
statementsto server failed: %s\n",             PQerrorMessage(conn));     exit_nicely(conn);   }  /* Loop through the
resultsof our statements. */ while (res = PQgetResult(conn))   {     /* Produce a "nice" header" */
printf("\n%s\nResultsof 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@meme.com>
Free Software:  "You don't pay back, you pay forward."                -- Robert A. Heinlein