Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result contains a large number of rows.
PL/pgSQL functions can return cursors to the caller. This is useful to return multiple rows or columns, especially with very large result sets. To do this, the function opens the cursor and returns the cursor name to the caller (or simply opens the cursor using a portal name specified by or otherwise known to the caller). The caller can then fetch rows from the cursor. The cursor can be closed by the caller, or it will be closed automatically when the transaction closes.
PgDAC supports reading cursors returned from stored procedures. The TPgStoredProc component opens automatically the first REFCURSOR returned from a stored procedure. For example, consider the following procedure:
CREATE FUNCTION cursor_func() RETURNS REFCURSOR AS $$
DECLARE
ref REFCURSOR;
BEGIN
OPEN ref FOR SELECT * FROM test;
RETURN ref;
END;
$$ LANGUAGE plpgsql;
You can read data from the returned cursor using the following code:
PgConnection.StartTransaction;
PgStoredProc.StoredProcName := 'cursor_func';
PgStoredProc.Open;
while not PgStoredProc.Eof do begin
Value := PgStoredProc.Fields[0].AsInteger;
...
PgStoredProc.Next;
end;
PgStoredProc.Close;
PgConnection.Commit;
Note that using cursors requires a transaction. So that StartTransaction is called before the Open method of TPgStoredProc.
If a stored procedure returns several REFCURSOR parameters, only first cursor is opened when you call the Open method of TPgStoredProc. To open the rest of cursors you can use the OpenNext method, or manipulate with TPgRefCursor instances. For example:
CREATE FUNCTION cursor_func(c1 INOUT REFCURSOR, c2 INOUT REFCURSOR) RETURNS RECORD AS $$
BEGIN
OPEN c1 FOR SELECT * FROM test1;
OPEN c2 FOR SELECT * FROM test2;
END;
$$ LANGUAGE plpgsql;
You can read data using the following code:
PgConnection.StartTransaction;
PgStoredProc.StoredProcName := 'cursor_func';
PgStoredProc.Open;
repeat
while not PgStoredProc.Eof do begin
Value := PgStoredProc.Fields[0].AsInteger;
...
PgStoredProc.Next;
end;
until not PgStoredProc.OpenNext;
PgStoredProc.Close;
PgConnection.Commit;
You can open both cursors at the same time by assigning a TPgRefCursor instance to the Cursor property of a dataset:
var
Cursor: TPgRefCursor;
PgQuery: TPgQuery;
...
PgConnection.StartTransaction;
PgStoredProc.StoredProcName := 'cursor_func';
PgStoredProc.Open;
Cursor := PgStoredProc.ParamByName('C2').AsCursor;
PgQuery.Cursor := Cursor;
PgQuery.Open; // open the second cursor
Value1 := PgStoredProc.Fields[0].AsInteger;
Value2 := PgQuery.Fields[0].AsInteger;
...
PgStoredProc.Close;
PgQuery.Close;
PgConnection.Commit;