Delphi/FireDac + PostgreSQL + stored procedure + Procedure (To call a procedure, use CALL)

Question

We are putting postgreSQL database support on our system, we currently work with Oracle.

We are trying to call a procedure in the database, just as we call in Oracle.

var conBanco := TFDConnection.Create(Self);
try
   conBanco.Params.Database := 'Database';
   conBanco.Params.UserName := 'UserName';
   conBanco.Params.Password := 'Password';
   conBanco.Params.Values['Server'] := 'IP';
   conBanco.Params.DriverID := 'PG';
   conBanco.Open();

   var stpBanco := TFDStoredProc.Create(Self);
   try
      stpBanco.Connection := conBanco;
      stpBanco.StoredProcName := 'gerador_padrao';
      stpBanco.Prepare;
      stpBanco.ParamByName('gerador').Value := 'pessoas';
      stpBanco.ExecProc();
      ShowMessage(VarToStrDef(stpBanco.ParamByName('parchave').Value, ''));
   finally
      stpBanco.Free;
   end;
finally
   conBanco.Free;
end;

However we get the following error:

exception class : Exception

exception message : EPgNativeException: [FireDAC][Phys][PG][libpq] ERROR: superus.gerador_padrao(gerador => character varying, parchave => numeric) is a procedure. To call a procedure, use CALL.

Stored procedure in database:

CREATE OR REPLACE PROCEDURE superus.gerador_padrao
     (gerador character varying, INOUT parchave numeric)
LANGUAGE plpgsql
AS $procedure$
begin
   --Code
end;
$procedure$
;

The error occurs on the following line:

stpBanco.Prepare;

The above code works perfectly in Oracle, how do I call the procedure in PostgreSQL?

Thank you.

Answer 1

Below we will talk about Delphi 10.2.3, maybe in later versions it's different.

Inside the file FireDAC.Phys.PGMeta.pas has a TFDPhysPgCommandGenerator.GetStoredProcCall method, inside which the procedure text is formed and there is no option to call the procedure via CALL. Moreover, there is no "CALL " text in this entire file. Maybe this is a mistake, or maybe some kind of cunning idea... But that doesn't make it any easier for us.

  // function returns void
  if not lHasOut then begin
    if FCommandKind = skStoredProc then
      FCommandKind := skStoredProcNoCrs;
    // NULL to avoid problem with execution of a
    // void function in binary result format
    Result := 'SELECT NULL FROM ';
  end
  else begin
    if lHasCrs and (FCommandKind = skStoredProc) then
      FCommandKind := skStoredProcWithCrs
    else if lFunc then
      lFunc := FCommandKind in [skStoredProc, skStoredProcNoCrs];
    if lFunc then
      Result := 'SELECT '
    else
      Result := 'SELECT * FROM ';
  end;

In total, here I see 2 options:

1. Form the procedure call string yourself. I'm sure it's not difficult, although, of course, it's more correct when the component does it.
2. Instead of a procedure, make a function with the same content, and make your output parameter the result. It is important to remember - the procedure call then must pass through ExecFunc in this case;