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

On Wed, 10 Jul 2024 at 11:04, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wednesday, July 10, 2024, Dave Cramer <davecramer(at)gmail(dot)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

>
>