Thread

1. Is it possible to create a cursor with hold using extended query protocol

   Dave Cramer <davecramer@gmail.com> — 2024-07-10T14:50:45Z

   Greetings,
   
   There are suggestions that you can use extended query to fetch from a
   cursor, however I don't see how you can bind values to the cursor ?
   
   PostgreSQL: Documentation: 16: FETCH
   <https://www.postgresql.org/docs/16/sql-fetch.html>
   
   Is this possible?
   
   Dave Cramer
   

2. Re: Is it possible to create a cursor with hold using extended query protocol

   David G. Johnston <david.g.johnston@gmail.com> — 2024-07-10T15:04:15Z

   On Wednesday, July 10, 2024, Dave Cramer <davecramer@gmail.com> wrote:
   
   > Greetings,
   >
   > There are suggestions that you can use extended query to fetch from a
   > cursor, however I don't see how you can bind values to the cursor ?
   >
   > PostgreSQL: Documentation: 16: FETCH
   > <https://www.postgresql.org/docs/16/sql-fetch.html>
   >
   > Is this possible?
   >
   
   Not that i can see.  The declare’d query isn’t shown to accept $n bindings
   rather it must be executable (select or values).  Per the note on declare,
   the bind phase of the fetch command under the extended protocol is used to
   determine whether values retrieved are text or binary.  Beyond that, the
   bind is really just a formality of the protocol, the same as for executing
   any other non-parameterized query that way.
   
   David J.
   

3. Re: Is it possible to create a cursor with hold using extended query protocol

   Dave Cramer <davecramer@gmail.com> — 2024-07-10T15:28:49Z

   On Wed, 10 Jul 2024 at 11:04, David G. Johnston <david.g.johnston@gmail.com>
   wrote:
   
   > On Wednesday, July 10, 2024, Dave Cramer <davecramer@gmail.com> wrote:
   >
   >> Greetings,
   >>
   >> There are suggestions that you can use extended query to fetch from a
   >> cursor, however I don't see how you can bind values to the cursor ?
   >>
   >> PostgreSQL: Documentation: 16: FETCH
   >> <https://www.postgresql.org/docs/16/sql-fetch.html>
   >>
   >> Is this possible?
   >>
   >
   > Not that i can see.  The declare’d query isn’t shown to accept $n bindings
   > rather it must be executable (select or values).  Per the note on declare,
   > the bind phase of the fetch command under the extended protocol is used to
   > determine whether values retrieved are text or binary.  Beyond that, the
   > bind is really just a formality of the protocol, the same as for executing
   > any other non-parameterized query that way.
   >
   
   Seems you can bind to the Declare though.
   
   execute <unnamed>: BEGIN
   2024-07-10 11:18:57.247 EDT [98519] LOG:  duration: 0.239 ms  parse
   <unnamed>: DECLARE c1 CURSOR WITH HOLD FOR select * from vactbl where id <
   $1
   2024-07-10 11:18:57.247 EDT [98519] LOG:  duration: 0.014 ms  bind
   <unnamed>: DECLARE c1 CURSOR WITH HOLD FOR select * from vactbl where id <
   $1
   2024-07-10 11:18:57.247 EDT [98519] DETAIL:  Parameters: $1 = '400'
   2024-07-10 11:18:57.248 EDT [98519] LOG:  duration: 1.080 ms  execute
   <unnamed>: DECLARE c1 CURSOR WITH HOLD FOR select * from vactbl where id <
   $1
   2024-07-10 11:18:57.248 EDT [98519] DETAIL:  Parameters: $1 = '400'
   
   Thanks,
   
   Dave
   
   >
   >
   

4. Re: Is it possible to create a cursor with hold using extended query protocol

   David G. Johnston <david.g.johnston@gmail.com> — 2024-07-10T15:56:04Z

   On Wed, Jul 10, 2024 at 8:29 AM Dave Cramer <davecramer@gmail.com> wrote:
   
   >
   > On Wed, 10 Jul 2024 at 11:04, David G. Johnston <
   > david.g.johnston@gmail.com> wrote:
   >
   >> On Wednesday, July 10, 2024, Dave Cramer <davecramer@gmail.com> wrote:
   >>
   >>> Greetings,
   >>>
   >>> There are suggestions that you can use extended query to fetch from a
   >>> cursor, however I don't see how you can bind values to the cursor ?
   >>>
   >>> PostgreSQL: Documentation: 16: FETCH
   >>> <https://www.postgresql.org/docs/16/sql-fetch.html>
   >>>
   >>> Is this possible?
   >>>
   >>
   >> Not that i can see.  The declare’d query isn’t shown to accept $n
   >> bindings rather it must be executable (select or values).  Per the note on
   >> declare, the bind phase of the fetch command under the extended protocol is
   >> used to determine whether values retrieved are text or binary.  Beyond
   >> that, the bind is really just a formality of the protocol, the same as for
   >> executing any other non-parameterized query that way.
   >>
   >
   > Seems you can bind to the Declare though.
   >
   >
   Right.  You got me trying to equate cursors and prepared statements and
   they differ in exactly this way.  The prepared binds are held until execute
   while cursor binds, and query execution for that matter, are immediate,
   with fetch just being a way to obtain the rows already computed (at least
   conceptually, optimizations might exist).  They both end up creating a
   named portal.  You cannot declare an execute command which simplifies
   things a bit.
   
   David J.