Re: Prepared select - Mailing list pgsql-hackers

libpq doesn't have enought support to allow executing a prepared statement
in a named portal (current libpq only works wuth the unnamed portal). But
the V3 protocol have it. I solved this problem by adding the following
functions. They let you prepare a named statement, execute this statement in
a named portal, fetch from it and close it.

this is a temporary solution to wait for an official extension of libpq
(more call could be added to support completly the V3 protocol).
   cyril


/** PQportalSetup*  Setup a portal to execute a prepared statement*/
PGresult *
PQportalSetup(PGconn *conn,     const char *stmtName,     const char *portalName,     int nParams,     const char
*const* paramValues,     const int *paramLengths,     const int *paramFormats,     int resultFormat)
 
{int   i;
if (!PQexecStart(conn)) return NULL;
if (!PQsendQueryStart(conn)) return NULL;
if (!stmtName){ printfPQExpBuffer(&conn->errorMessage,       libpq_gettext("statement name is a null pointer\n"));
returnNULL;}
 
/* This isn't gonna work on a 2.0 server */if (PG_PROTOCOL_MAJOR(conn->pversion) < 3){
printfPQExpBuffer(&conn->errorMessage,  libpq_gettext("function requires at least protocol version 3.0\n")); return
0;}
/* construct the Bind message */if (pqPutMsgStart('B', false, conn) < 0 || pqPuts(portalName, conn) < 0 ||
pqPuts(stmtName,conn) < 0) goto sendFailed;if (nParams > 0 && paramFormats){ if (pqPutInt(nParams, 2, conn) < 0)  goto
sendFailed;for (i = 0; i < nParams; i++) {  if (pqPutInt(paramFormats[i], 2, conn) < 0)   goto sendFailed; }}else{ if
(pqPutInt(0,2, conn) < 0)  goto sendFailed;}if (pqPutInt(nParams, 2, conn) < 0) goto sendFailed;for (i = 0; i <
nParams;i++){ if (paramValues && paramValues[i]) {  int   nbytes;
 
  if (paramFormats && paramFormats[i] != 0)  {   /* binary parameter */   nbytes = paramLengths[i];  }  else  {   /*
textparameter, do not use paramLengths */   nbytes = strlen(paramValues[i]);  }  if (pqPutInt(nbytes, 4, conn) < 0 ||
pqPutnchar(paramValues[i],nbytes, conn) < 0)   goto sendFailed; } else {  /* take the param as NULL */  if
(pqPutInt(-1,4, conn) < 0)   goto sendFailed; }}if (pqPutInt(1, 2, conn) < 0 || pqPutInt(resultFormat, 2, conn)) goto
sendFailed;if(pqPutMsgEnd(conn) < 0) goto sendFailed;
 
/* construct the Sync message */if (pqPutMsgStart('S', false, conn) < 0 || pqPutMsgEnd(conn) < 0) goto sendFailed;
/* remember we are using extended query protocol */conn->ext_query = true;
/* * Give the data a push.  In nonblock mode, don't complain if we're * unable to send it all; PQgetResult() will do
anyadditional * flushing needed. */if (pqFlush(conn) < 0) goto sendFailed;
 
/* OK, it's launched! */conn->asyncStatus = PGASYNC_BUSY;
return PQexecFinish(conn);

sendFailed:pqHandleSendFailure(conn);return NULL;
}

/** PQportalFetch*  Fetch next rows*/
PGresult *
PQportalFetch(PGconn *conn,     const char *portalName,     int maxrows)
{if (!PQexecStart(conn)) return NULL;
if (!PQsendQueryStart(conn)) return NULL;
/* This isn't gonna work on a 2.0 server */if (PG_PROTOCOL_MAJOR(conn->pversion) < 3){
printfPQExpBuffer(&conn->errorMessage,  libpq_gettext("function requires at least protocol version 3.0\n")); return
0;}
/* construct the Describe Portal message */if (pqPutMsgStart('D', false, conn) < 0 || pqPutc('P', conn) < 0 ||
pqPuts(portalName,conn) < 0 || pqPutMsgEnd(conn) < 0) goto sendFailed;
 
/* construct the Execute message */if (pqPutMsgStart('E', false, conn) < 0 || pqPuts(portalName, conn) < 0 ||
pqPutInt(maxrows,4, conn) < 0 || pqPutMsgEnd(conn) < 0) goto sendFailed;
 
/* construct the Sync message */if (pqPutMsgStart('S', false, conn) < 0 || pqPutMsgEnd(conn) < 0) goto sendFailed;
/* remember we are using extended query protocol */conn->ext_query = true;
/* * Give the data a push.  In nonblock mode, don't complain if we're * unable to send it all; PQgetResult() will do
anyadditional * flushing needed. */if (pqFlush(conn) < 0) goto sendFailed;
 
/* OK, it's launched! */conn->asyncStatus = PGASYNC_BUSY;return PQexecFinish(conn);

sendFailed:pqHandleSendFailure(conn);return NULL;
}

/** PQportalClose*  Close a named portal*  using protocol 3.0*/
PGresult *
PQportalClose(PGconn *conn,     const char *portalName)
{if (!PQexecStart(conn)) return NULL;
if (!PQsendQueryStart(conn)) return NULL;
/* This isn't gonna work on a 2.0 server */if (PG_PROTOCOL_MAJOR(conn->pversion) < 3){
printfPQExpBuffer(&conn->errorMessage,  libpq_gettext("function requires at least protocol version 3.0\n")); return
0;}
/* construct the Close message */if (pqPutMsgStart('C', false, conn) < 0 || pqPutc('P', conn) < 0 || pqPuts(portalName,
conn)< 0 || pqPutMsgEnd(conn) < 0) goto sendFailed;
 
/* construct the Sync message */if (pqPutMsgStart('S', false, conn) < 0 || pqPutMsgEnd(conn) < 0) goto sendFailed;
/* remember we are using extended query protocol */conn->ext_query = true;
/* * Give the data a push.  In nonblock mode, don't complain if we're * unable to send it all; PQgetResult() will do
anyadditional * flushing needed. */if (pqFlush(conn) < 0) goto sendFailed;
 
/* OK, it's launched! */conn->asyncStatus = PGASYNC_BUSY;return PQexecFinish(conn);

sendFailed:pqHandleSendFailure(conn);return NULL;
}




----- Original Message ----- 
From: "Robert Turnbull" <rturnbull@strategicmind.com>
To: "Christoph Haller" <ch@rodos.fzk.de>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, April 19, 2004 2:48 AM
Subject: Re: [HACKERS] Prepared select


> There are several production issues related to the proposed solution. For
> example, what happens when the result set exceeds the swap space of the
> server or client machine? My original question is how to get a cursor from
a
> prepared select so a subset of the result can be returned to the client
for
> processing. For your solution to work the SQL EXECUTE command needs the
> functionality of  the SQL FETCH command.
>
>
> > >
> > >
> > > How can I use a prepared select statement as mentioned in the
> documentation=
> > >  on SQL PREPARE. Preparing the statement is easy, the problem is using
> the =
> > > plan to get a cursor. My assumption is the SQL OPEN command is not
> document=
> > > ed or there is some other libpq API to make this happen.
> > >
> > > Thanks
> > >
> > >
> > >
> > I'm using libpq and lines like below are working:
> >
> > res = PQexec(conn,
> > "PREPARE plan001 ( integer , double precision , character ) AS SELECT
> a,b,d FROM foo WHERE a = $1 OR d > $2 OR b = $3");
> > ...
> > res = PQexec(conn, "EXECUTE plan001 ( 3 , 6.66 , 'whatever' ) ");
> >
> > HTH, pretty late reply - I know (but no one else did as far as I can
tell)
> >
> > Regards, Christoph
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>