Re: Is it possible to create a cursor with hold using extended query protocol - Mailing list pgsql-hackers

From Dave Cramer
Subject Re: Is it possible to create a cursor with hold using extended query protocol
Date
Msg-id CADK3HHJhaBUzr7OD3+TiESaTKRtf3BScsOpctJ66q7c35a1Trg@mail.gmail.com
Whole thread Raw
In response to Re: Is it possible to create a cursor with hold using extended query protocol  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: Is it possible to create a cursor with hold using extended query protocol
List pgsql-hackers

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 ?


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 

pgsql-hackers by date:

Previous
From: "David E. Wheeler"
Date:
Subject: Re: jsonpath: Inconsistency of timestamp_tz() Output
Next
From: Nathan Bossart
Date:
Subject: Re: Add a GUC check hook to ensure summarize_wal cannot be enabled when wal_level is minimal