Re: 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