Using Stored Functions with Result Sets

This tutorial describes how to retrieve and modify result sets obtained from stored functions using the TPgStoredProc component

Requirements

This tutorial assumes that you have already connected to the server (see Connecting to PostgreSQL), created the necessary objects on the server (see Creating Database Objects), and inserted data into tables (see Inserting Data Into Tables). To insert data at runtime, add the PgAccess unit to the uses clause for Delphi or include the PgAccess.hpp header file for C++ Builder.

Besides scalar variables, a stored function can return a result set generated by the SELECT statement. You can insert or modify data in a result set using the dataset functionality of the TPgStoredProc component.

This tutorial shows you how to retrieve and modify data in the dept table using the TPgStoredProc component. The following stored procedure will be used to retrieve data:

CREATE FUNCTION TenMostHighPaidEmployees()
RETURNS SETOF Emp AS $$
  SELECT * FROM dept;
$$ LANGUAGE sql;

Using the DataSet Functionality

The Insert, Append, Edit, and Post methods of the TPgStoredProc component can be used to insert and modify data without having to manually construct a DML statement — it is generated by PgDAC components internally. The code below demonstrates the use of these methods:

Delphi

var
  PgStoredProc11: TPgStoredProc;
begin
  PgStoredProc1 := TPgStoredProc.Create(nil);
  try
    // Pgonnection1 was set up earlier
    PgStoredProc1.Connection := PgConnection1;
    // indicates the name of the stored procedure to call 
    PgStoredProc1.StoredProcName := 'SelectDept';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    PgStoredProc1.PrepareSQL;
    // opens the dataset
    PgStoredProc1.Open;
    // adds a new empty record at the end of the dataset
    PgStoredProc1.Append;
    // searches fields by their names and assigns new values
    PgStoredProc1.FieldByName('deptno').AsInteger := 50;
    PgStoredProc1.FieldByName('dname').AsString := 'SALES';
    PgStoredProc1.FieldByName('loc').AsString := 'NEW YORK';
    // writes the modified record
    PgStoredProc1.Post;
    // adds a new empty record in the current cursor position
    PgStoredProc1.Insert;
    PgStoredProc1.FieldByName('deptno').AsInteger := 60;
    PgStoredProc1.FieldByName('dname').AsString := 'ACCOUNTING';
    PgStoredProc1.FieldByName('loc').AsString := 'LOS ANGELES';
    PgStoredProc1.Post;
    // positions the cursor on the deptno=10 record
    PgStoredProc1.FindKey([10]);
    // enables editing of data in the dataset
    PgStoredProc1.Edit;
    PgStoredProc1.FieldByName('dname').AsString := 'RESEARCH';
    PgStoredProc1.FieldByName('loc').AsString := 'LOS ANGELES';
    PgStoredProc1.Post;
  finally
    PgStoredProc1.Free;
  end;
end;

C++Builder

TPgStoredProc* PgStoredProc1 = new TPgStoredProc(NULL);
try {
    // PgConnection1 was set up earlier
    PgStoredProc1->Connection = PgConnection1;
    // indicates the name of the stored procedure to call 
    PgStoredProc1->StoredProcName = "SelectDept";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    PgStoredProc1->PrepareSQL();
    // opens the dataset
    PgStoredProc1->Open();
    // adds a new empty record at the end of the dataset
    PgStoredProc1->Append();
    // searches fields by their names and assigns new values
    PgStoredProc1->FieldByName("deptno")->AsInteger = 50;
    PgStoredProc1->FieldByName("dname")->AsString = "SALES";
    PgStoredProc1->FieldByName("loc")->AsString = "NEW YORK";
    // writes the modified record
    PgStoredProc1->Post();
    // adds a new empty record in the current cursor position
    PgStoredProc1->Insert();
    PgStoredProc1->FieldByName("deptno")->AsInteger = 60;
    PgStoredProc1->FieldByName("dname")->AsString = "ACCOUNTING";
    PgStoredProc1->FieldByName("loc")->AsString = "LOS ANGELES";
    PgStoredProc1->Post();
    // positions the cursor on the deptno=10 record
    PgStoredProc1->FindKey(ARRAYOFCONST((10)));
    // enables editing of data in the dataset
    PgStoredProc1->Edit();
    PgStoredProc1->FieldByName("dname")->AsString = "RESEARCH";
    PgStoredProc1->FieldByName("loc")->AsString = "LOS ANGELES";
    PgStoredProc1->Post();
}
__finally {
    PgStoredProc1->Free();
}