Re: Protocol question regarding Portal vs Cursor - Mailing list pgsql-hackers

> So while the API's are "virtually" identical AFAICT there is no way to
> create a "WITH HOLD" portal ?

I am not sure if I fully understand your question but I think you can
create a portal with "WITH HOLD" option.

BEGIN;
DECLARE c CURSOR WITH HOLD FOR SELECT * FROM generate_series(1,10);

(of course you could use extended query protocol instead of simple
query protocol here)

After this there's portal named "c" in the backend with WITH HOLD
attribute. And you could issue a Describe message against the portal.
Also you could issue an Execute messages to fetch N rows (N can be
specified in the Execute message) with or without in a transaction
because WITH HOLD is specified.

Here is a sample session. The generate_series() generates 10 rows. You
can fetch 5 rows from portal "c" inside the transaction. After the
transaction closed, you can fetch remaining 5 rows as expected.

FE=> Query (query="BEGIN")
<= BE CommandComplete(BEGIN)
<= BE ReadyForQuery(T)
FE=> Query (query="DECLARE c CURSOR WITH HOLD FOR SELECT * FROM generate_series(1,10)")
<= BE CommandComplete(DECLARE CURSOR)
<= BE ReadyForQuery(T)
FE=> Describe(portal="c")
FE=> Execute(portal="c")
FE=> Sync
<= BE RowDescription
<= BE DataRow
<= BE DataRow
<= BE DataRow
<= BE DataRow
<= BE DataRow
<= BE PortalSuspended
<= BE ReadyForQuery(T)
FE=> Query (query="END")
<= BE CommandComplete(COMMIT)
<= BE ReadyForQuery(I)
FE=> Execute(portal="c")
FE=> Sync
<= BE DataRow
<= BE DataRow
<= BE DataRow
<= BE DataRow
<= BE DataRow
<= BE PortalSuspended
<= BE ReadyForQuery(I)
FE=> Terminate

Best reagards,