How to execute function with parameter in PostgreSQL using anonymous code block in FireDAC?

Question

My database (PostgreSQL 11) has a function that must be called to execute an action. The return is not important, as long as there's no error. This function has arguments that must be passed as parameters. I cannot directly use TFDConnection.ExecSQL because I use parameters of type array that is not supported by the method (to my knowledge). So, I use TFDQuery.ExecSQL like this:

msql1: = 'DO $$ BEGIN PERFORM DoIt (:id,:items); END $$; ';
    
{... create FDQuery (fq), set connection, set SQL.Text to msql1}
      
fq.Params.ParamByName ('id'). AsInteger: = 1;
    
{$ REGION 'items'}
fq.ParamByName ('items'). DataType: = ftArray;
fq.ParamByName ('items'). ArrayType: = atTable; // Must be atTable, not atArray
if length (items)> 0 then
begin
  fq.ParamByName ('items'). ArraySize: = length (items);
  for it: = 0 to length (items) -1 do
  fq.ParamByName ('items'). AsIntegers [it]: = items [it];
end;
fq.ExecSQL;
{$ ENDREGION}

When executing the above code, the error above message raises

"Parameter 'id' not found".

After some research that suggested using fq.Params.ParamByName I was also unsuccessful.

However, if you change the way the function is called to select DoIt (:id,:items); and obviously replacing the execution with fq.Open works perfectly. Is it possible to execute a PL / pgSQL block that contains parameters in the function called by this block using TFDConnection / TFDQuery?

PS: I'm using Delphi Rio 10.3.3

Answer 1

When you assign a value to TFDQuery.SQL, FireDAC does some pre-processing of the SQL based on various options. ResourceOptions.CreateParams option controls whether parameters should be processed. This is enabled by default.

The preprocessor recognizes string literals in your SQL and doesn't try to look for the parameters in them. You used dollar quoted string constant and that's why FireDAC doesn't recognize parameters in it. Even if you add parameter manually I think that FireDAC would not bind the value.

With that said, the proper way to execute stored procedures/function is to use TFDStoredProc. You just assign StoredProcName and call its Prepare method which will retrieve procedure's metadata (parameters) from database so you don't need to set ArrayType or DataType of the parameter.

In your code you set the DataType to ftArray which is wrong, because in case of array parameter it should be set to array's element type. Anyway, by setting fq.ParamByName ('items').AsIntegers you effectively set parameter's DataType to ftInteger. All you need to do is to set ArraySize

Here's what you should do instead:

procedure DoIt(Connection: TFDConnection; ID: Integer; const Items: TArray<Integer>);
var
  StoredProc: TFDStoredProc;
  ParamItems: TFDParam;
  Index: Integer;
begin
  StoredProc := TFDStoredProc.Create(nil);
  try
    StoredProc.Connection := Connection;
    StoredProc.StoredProcName := 'DoIt';
    StoredProc.Prepare;
    StoredProc.Params.ParamByName('id').AsInteger := ID;
    if Length(Items) > 0 then
    begin
      ParamItems := StoredProc.Params.ParamByName('items');
      ParamItems.ArraySize := Length(Items);
      for Index := Low(Items) to High(Items) do
        ParamItems.AsIntegers[Index] := Items[Index];
    end;
    StoredProc.ExecProc;
  finally
    StoredProc.Free;
  end;
end;

Alternatively you can use ExecFunc to get the result of stored function.