Re: some hints to understand the plsql cursor.

On Wed, Feb 27, 2019 at 4:42 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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