PgDAC

Using Stored Procedures

This tutorial describes how to work with stored procedures using the TPgStoredProc component to insert data into tables.

  1. Requirements
  2. General information
  3. Input Parameters
  4. Output Parameters
  5. Input/Output Parameters
  6. Using PostgreSQL Stored Functions
  7. Returning Result Sets

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.

General Information

A stored procedure is a group of one or more SQL statements grouped as a logical unit and stored in the database. Stored procedures are intended to perform a specific task or a set of related tasks. They combine the ease and flexibility of the SQL language with the procedural functionality of a structured programming language. Complicated business rules and programming logic that may require execution of multiple SQL statements should be kept in stored procedures, which can be called by the client applications.

A stored function is similar to a stored procedure, but there are some differences: a function must return a value, whereas in a stored procedure it is optional; a function can have only input parameters, whereas a procedures can have input or output parameters; a function can be called from a procedure, whereas a procedure cannot be called from a function.

Input parameters

Input parameters are used to pass values from the calling program to the stored procedure or function. If the procedure changes the input value, the change has effect only within the procedure, and the input variable will preserve its original value when control is returned to the calling program. The following procedure inserts a new row into the table dept:

CREATE PROCEDURE InsertDept(
  p_deptno int,
  p_dname varchar(14),
  p_loc varchar(13)
)
AS $$
  INSERT INTO dept(deptno, dname, loc) VALUES(:p_deptno, :p_dname, :p_loc);
$$ LANGUAGE sql;

The procedure accepts three input arguments that correspond to the fields of the table, and can be executed as follows:

CALL InsertDept(10,'ACCOUNTING','NEW YORK');

The code below demonstrates the use of the TPgStoredProc component to execute the InsertDept stored procedure:

Delphi

var
  PgStoredProc1: TPgStoredProc;
begin
  PgStoredProc1 := TPgStoredProc.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgStoredProc1.Connection := PgConnection1;
    // indicates the name of the stored procedure to call
    PgStoredProc1.StoredProcName := 'InsertDept';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    PgStoredProc1.PrepareSQL;
    // searches parameters by their names and assigns new values
    PgStoredProc1.ParamByName('p_deptno').AsInteger := 10;
    PgStoredProc1.ParamByName('p_dname').AsString := 'ACCOUNTING';
    PgStoredProc1.ParamByName('p_loc').AsString := 'NEW YORK';
    // executes the stored procedure
    PgStoredProc1.Execute;
  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 = "InsertDept";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    PgStoredProc1->PrepareSQL();
    // searches parameters by their names and assigns new values
    PgStoredProc1->ParamByName("p_deptno")->AsInteger = 10;
    PgStoredProc1->ParamByName("p_dname")->AsString = "ACCOUNTING";
    PgStoredProc1->ParamByName("p_loc")->AsString = "NEW YORK";
    // executes the stored procedure
    PgStoredProc1->Execute();
}
__finally {
    PgStoredProc1->Free();
}

Output Parameters

Output parameters are used to return values from the procedure or function to the calling application. The initial value of the parameter in the procedure is NULL, and the value becomes visible to the calling program only when the procedure returns it. PostgreSQL supports output parameters only in stored functions. The following stored function returns the count of records in the dept table:

CREATE FUNCTION CountDept(cnt int)
RETURNS int AS $$
BEGIN
  RETURN (SELECT count(*) FROM dept);
END;
$$ LANGUAGE plpgsql;

The code below demonstrates the use of the TIBCStoredProc component to execute the CountDept stored procedure:

Delphi

var
  IBCStoredProc1: TIBCStoredProc;
begin
  IBCStoredProc1 := TIBCStoredProc.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCStoredProc1.Connection := IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1.StoredProcName := 'CountDept';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1.PrepareSQL;
    // executes the stored procedure
    IBCStoredProc1.Execute;
    // shows the value of the output parameter
    ShowMessage(IntToStr(sp.ParamByName('cnt').AsInteger));
  finally
    IBCStoredProc1.Free;
  end;
end;

C++Builder

TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
    // IBCConnection1 was set up earlier
    IBCStoredProc1->Connection = IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1->StoredProcName = "CountDept";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1->PrepareSQL();
    // executes the stored procedure
    IBCStoredProc1->Execute();
    // shows the value of the output parameter
    ShowMessage(IntToStr(sp->ParamByName("cnt")->AsInteger));
}
__finally {
    IBCStoredProc1->Free();
}

Input/output parameters

Input/output parameters (INOUT) act as the IN or OUT parameter, or both. Programs can pass a value to the stored procedure or function, which changes it and returns the updated value. The input value must be set before executing the stored procedure. INOUT parameters act like an initialized variables.

The following stored procedure returns the salary with a 5% percent bonus:

CREATE PROCEDURE GiveBonus(INOUT sal real)
AS $$
BEGIN
  sal = sal * 1.05;
END;
$$ LANGUAGE plpgsql;

The code below demonstrates the use of the TPgStoredProc component to execute the GiveBonusFunc stored function:

Delphi

var
  PgStoredProc1: TPgStoredProc;
begin
  PgStoredProc1 := TPgStoredProc.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgStoredProc1.Connection := PgConnection1;
    // indicates the name of the stored procedure to call
    PgStoredProc1.StoredProcName := 'GiveBonus';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    PgStoredProc1.PrepareSQL;
    // searches a parameter by its name and assigns a new value
    PgStoredProc1.ParamByName('sal').AsFloat := 500.5;
    // executes the stored procedure
    PgStoredProc1.Execute;
    // shows the resulting value
    ShowMessage(PgStoredProc1.ParamByName('sal').AsString);
  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 = "GiveBonus";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    PgStoredProc1->PrepareSQL();
    // searches a parameter by its name and assigns a new value
    PgStoredProc1->ParamByName("sal")->AsFloat = 500.5;
    // executes the stored procedure
    PgStoredProc1->Execute();
    // shows the resulting value
    ShowMessage(PgStoredProc1->ParamByName("sal")->AsString);
}
__finally {
    PgStoredProc1>Free();
}

Using PostgreSQL Stored Functions

The tasks above can also be accomplished using stored functions in PostgreSQL. For example, the following stored function returns the salary with a 5% percent bonus:

CREATE FUNCTION GiveBonusFunc(sal real)
RETURNS real AS $$
BEGIN
  RETURN sal * 1.05;
END;
$$ LANGUAGE plpgsql;

This function can be executed as follows:

SELECT GiveBonusFunc(500.5);

The code below demonstrates the use of the TPgStoredProc component to execute the GiveBonusFunc stored function:

Delphi

var
  PgStoredProc1: TPgStoredProc;
begin
  PgStoredProc1 := TPgStoredProc.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgStoredProc1.Connection := PgConnection1;
    // indicates the name of the stored procedure to call
    PgStoredProc1.StoredProcName := 'GiveBonusFunc';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    PgStoredProc1.PrepareSQL;
    // searches a parameter by its name and assigns a new value
    PgStoredProc1.ParamByName('sal').AsFloat := 500.5;
    // executes the stored procedure
    PgStoredProc1.Execute;
    // shows the resulting value
    ShowMessage(PgStoredProc1.ParamByName('result').AsString);
  finally
    IBCStoredProc1.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 = "GiveBonusFunc";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    PgStoredProc1->PrepareSQL();
    // searches a parameter by its name and assigns a new value
    PgStoredProc1->ParamByName("sal")->AsFloat = 500.5;
    // executes the stored procedure
    PgStoredProc1->Execute();
    // shows the resulting value
    ShowMessage(PgStoredProc1->ParamByName("result")->AsString);
  }
__finally {
    PgStoredProc1->Free();
}
}

Note: To retrieve the result returned by the stored function using TPgStoredProc, use the automatically created 'result' parameter.

Returning Result Sets

Besides scalar variables, a stored function can return a result set generated by the SELECT statement. See Using Stored Functions with Result Sets for more information.

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