PostgreSQL cursor example

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

-- Drop function top100cur (); Create Function top100cur () returns refcursor as $ declare ABC refcursor; beginopen ABC for select * From person limit 100; return ABC; end $ language plpgsql; select top100cur (); -- returns the anonymous cursor -- fetch all from ABC; -- the 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)

-- Drop function top100cur (); Create Function top100cur () returns refcursor as $ declare ABC cursor for select * From person limit 100; beginopen ABC; return ABC; end $ language plpgsql; select top100cur (); -- returns the cursor fetch all from ABC; -- 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.

--drop function top100cur(refcursor);create function top100cur(refcursor) returns refcursor as $$beginopen $1 for select * from person limit 100;return $1;end$$language plpgsql;SELECT top100cur('abc');fetch all from abc;

4. A cursor named $1 is returned.

-- Drop function top100cur (refcursor); Create Function top100cur (refcursor) returns refcursor as $ declare $1 cursor for select * From person limit 100; beginopen $1; -- if it is not open, the returned name is "$1" and cannot be used! Return $1; end $ language plpgsql; select top100cur ('abc'); fetch all from "$1 ";

5. An anonymous cursor is returned.

-- Drop function top100cur (refcursor); Create Function top100cur (refcursor) returns refcursor as $ declare $1 refcursor; beginopen $1 for select * From person limit 100; return $1; end $ language plpgsql; select top100cur ('abc'); -- 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 ......)

--drop function top100cur(refcursor);create function top100cur(refcursor) returns refcursor as $$declare abcdef refcursor;beginopen $1 for select * from person limit 100;return $1;end$$language plpgsql;SELECT top100cur('abc');fetch all from "abc";

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

-- Drop function top100cur (refcursor); Create Function top100cur (refcursor) returns refcursor as $ declare abcdef refcursor; begin -- open defg for select * From person limit 100; -- error: "defg" is not a known variable -- Return defg; ----------- Line 5: Open defg for select * From person limit 100; end $ 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!

For a description of the cursor in PostgreSQL, see:

《PostgreSQL 8.1 Chinese Document (PostgreSQL ChinaProduction)Chapter 1