PgDAC

REFCURSOR Data Type

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;

© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback