FetchCursor_StoredFunction1.htm |
cursor is similar to processing a result set using a FOR loop, like "emp_cursor CURSOR FOR SELECT * FROM emp;" |
To use a cursor, you need to DECLARE the cursor, then use OPEN, FETCH, CLOSE cycle to retrieve the data. A FETCH statement is used to retrieve the result set (one row at a time). |
CREATE TABLE table_pgsql1 (col text); INSERT INTO table_pgsql1 VALUES ('Adam'); INSERT INTO table_pgsql1 VALUES ('Peter'); INSERT INTO table_pgsql1 VALUES ('Joseph'); CREATE FUNCTION func_refcurfsor1(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM table_pgsql1; RETURN $1; END; ' LANGUAGE plpgsql; |
With php you may call this function and cursor SELECT func_refcurfsor1('funccursor'); FETCH ALL IN funccursor; Cursor / rows are indexed. SELECT func_refcurfsor1('funccursor'); FETCH 1 IN funccursor |