some hints to understand the plsql cursor.

On Wed, Feb 27, 2019 at 4:42 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:

actually I'm hacking pg for a function like :
1. define a select query.
2. client ask for some data. and server reply some data. server will do NOTHING if client doesn't ask any more..
3. client ask some data more data with a batch and SERVER reply some data then. then do NOTHING.

currently the simple "select * from t", the server will try to send the data to client at one time which is not something I want.

by looking into the plsql, looks it has some api like:

fetch 10 from cursor_1;
fetch 10 from cursor_1;

I'm lacking of the experience to hack plsql. so my question are:
1. Does pg has some codes which act like the "ask -> reply -> ask again -> reply again" on the server code? currently I'm not sure if the above "fetch" really work like this.
2. any resources or hint or suggestion to understand the "fetch" statement?

I guess you are looking for these syntax?

postgres=# BEGIN;
BEGIN
postgres=# DECLARE cur CURSOR FOR SELECT * FROM t;
DECLARE CURSOR
postgres=# FETCH NEXT cur;
a
---
1
(1 row)

postgres=# FETCH 10 cur;
a
---
2
3
4
5
1
2
3
4
5
6
(10 rows)

postgres=# FETCH NEXT cur;
a
---
7
(1 row)

postgres=# CLOSE cur;
CLOSE CURSOR

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Thanks Kumar. actually I was asking what the the cursor did in the
server. By looking the code, looks it cache the previous Portal with the
name is the cursor name, whenever we run the fetch from the portal, it
will restore the previous Portal and run it.

But your minimized and interactive code definitely makes the debug much
quicker. Thank you very much!

On Wed, Feb 27, 2019 at 11:35 PM Dilip Kumar <dilipbalaut@gmail.com> wrote: