PostgreSQL cursor example

1. The following Code creates a top100cur () function, which returns an anonymous cursor.

1. -- Drop function top100cur ();
3. Create FunctionTop100cur ()ReturnsRefcursorAs$
4. Declare
5. Abc refcursor;
6. Begin
7. OpenAbcFor Select*FromPersons limit 100;
8. ReturnAbc;
9. End
10. $ Language plpgsql;
12. SELECTTop100cur ();-- Returns an anonymous cursor.
13. -- Fetch all from abc; -- anonymous cursor, so this row is incorrect

2. The following code will create a function and return a cursor named abc (Can you find the difference? Yes. Whether the cursor is anonymous depends on whether the cursor variable is bound to the open cursor)

1. -- Drop function top100cur ();
3. Create FunctionTop100cur ()ReturnsRefcursorAs$
4. Declare
5. AbcCursor For Select*FromPersons limit 100;
6. Begin
7. OpenAbc;
8. ReturnAbc;
9. End
10. $ Language plpgsql;
12. SELECTTop100cur ();-- Returns a cursor named abc.
13. Fetch All FromAbc;-- This row is correct. During execution, remember to drag this row to the blacklist before the select row, and then F5

3. Return the cursor named by the caller.

1. -- Drop function top100cur (refcursor );
2. Create FunctionTop100cur (refcursor)ReturnsRefcursorAs$
3. Begin
4. Open$1For Select*FromPersons limit 100;
5. Return$1;
6. End
7. $ Language plpgsql;
9. SELECTTop100cur ('Abc');
10. Fetch All FromAbc;

4. A cursor named $1 is returned.

1. -- Drop function top100cur (refcursor );
2. Create FunctionTop100cur (refcursor)ReturnsRefcursorAs$
3. Declare
4. $1Cursor For Select*FromPersons limit 100;
5. Begin
6. Open$1;-- If it is not open, the returned name is "$1" and the cursor cannot be used!
7. Return$1;
8. End
9. $ Language plpgsql;
11. SELECTTop100cur ('Abc');
12. Fetch All From "$1";

5. An anonymous cursor is returned.

1. -- Drop function top100cur (refcursor );
2. Create FunctionTop100cur (refcursor)ReturnsRefcursorAs$
3. Declare
4. $1 refcursor;
5. Begin
6. Open$1For Select*FromPersons limit 100;
7. Return$1;
8. End
9. $ Language plpgsql;
11. SELECTTop100cur ('Abc');
12. -- Fetch all from "$1"; -- anonymous cursor, so this row is incorrect

6. Return the cursor specified by the caller (Do you know the difference from 4 and 5? In 4 and 5, the cursor variable name declared by declare is too special, and the anonymous parameter name is hidden ......)

1. -- Drop function top100cur (refcursor );
2. Create FunctionTop100cur (refcursor)ReturnsRefcursorAs$
3. Declare
4. Abcdef refcursor;
5. Begin
6. Open$1For Select*FromPersons limit 100;
7. Return$1;
8. End
9. $ Language plpgsql;
11. SELECTTop100cur ('Abc');
12. Fetch All From "Abc";

7. open a cursor variable that is neither declare nor passed in.

1. -- Drop function top100cur (refcursor );
2. Create FunctionTop100cur (refcursor)ReturnsRefcursorAs$
3. Declare
4. Abcdef refcursor;
5. Begin
6. -- Open defg for select * from person limit 100; -- error: "defg" is not a known variable
7. -- Return defg; ----------- LINE 5: open defg for select * from person limit 100;
8. End
9. $ Language plpgsql;

Summary:

1. declare variables will cause function parameters to be hidden (for example, after declare $1, the first anonymous parameter will be hidden );

2. declare only declares a cursor and does not open the cursor. A cursor without open cannot be used ~

3. When you open the cursor after declare, if the cursor is unbound (no cursor for XXX When declare is used), an anonymous cursor is obtained after the cursor is opened;

4. The cursor variable of the open operation is either declare or a string passed in as a parameter. In addition, an error is returned!