ISSUE: PgSQL function is executed more than once

[binfch]

Hi there

I have a table:

Code: Select all

CREATE TABLE vinclient.tabletest (
  testnum  integer,
  testtxt  text
) WITH (OIDS = FALSE);

And I have a composite type:

Code: Select all

CREATE TYPE vinclient.t_test AS (
  testnum  integer,
  testtxt  text
);

And I have a PgSQL function:

Code: Select all

CREATE OR REPLACE FUNCTION vinclient.f_test
(
  IN  p_testnum  integer,
  IN  p_testtxt  text   
)
RETURNS vinclient.t_test AS
$$
declare
  t_return vinclient.t_test%rowtype;

begin
  insert into vinclient.tabletest (testnum, testtxt)
  values(p_testnum, p_testtxt);

  t_return.testnum = p_testnum;
  t_return.testtxt = p_testtxt;

  return t_return;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

Now I want to call this PgSQL function from Delphi:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  myPgStoredProc: TPgStoredProc;
  myTestNum: Integer;
  myTestTxt: AnsiString;
begin
  try
    myPgStoredProc := TPgStoredProc.Create(nil);
    with myPgStoredProc do begin
      Connection := PgConnection1;
      StoredProcName := 'f_test';
      Sql.Add('SELECT (vinclient.f_test(:p_testnum, :p_testtxt)).*');
      ParamByName('p_testnum').AsInteger := 1234;
      ParamByName('p_testtxt').AsString := 'Sample String';
      Execute;
      if RecordCount > 0 then begin
        myTestNum := Fields[0].AsInteger;
        myTestTxt := Fields[1].AsString;
      end;
      Close;
      ShowMessage('OK, only 1 record was posted to the database!');
    end;
  finally
    myPgStoredProc.Free;
  end;
end;

-> The problem is that the PgSQL function seems to be called twice! There are 2 new records in my table.

What is wrong here?

Thx & cheers,
Peter

[ZEuS]

Please, specify the exact version of PgDAC you are using.

[binfch]

Version 3.2.8

[ZEuS]

To avoid multiple execution, you should call the function as SELECT f_test(:p_testnum, :p_testtxt);
There is no need to use a ".*", because the function will return data anyway, and the use of ".*" causes double execution of the function.
This is a specificity of PostgreSQL, because we have also reproduced this behavior using any database tools.

[binfch]

Hi there

Unfortunately this does not work with FPC/Lazarus under Windows (it does also not work under Linux) -> It gives an exception "Unknown field type: f_test"

Here's the whole test-prg:

Devart 3.2.8
Lazarus 0.9.30.4
FreePascal 2.6.0

Code: Select all

unit Unit1; 

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  PgAccess;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    PgConnection1: TPgConnection;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end; 

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
  myPgStoredProc: TPgStoredProc;
  myTestNum: Integer;
  myTestTxt: AnsiString;
begin
  try
    myPgStoredProc := TPgStoredProc.Create(nil);
    with myPgStoredProc do begin
      Connection := PgConnection1;
      StoredProcName := 'f_test';
      Sql.Add('SELECT vinclient.f_test(:p_testnum, :p_testtxt)');
      ParamByName('p_testnum').AsInteger := 1234;
      ParamByName('p_testtxt').AsString := 'Sample String';
      Execute;
      if RecordCount > 0 then begin
        myTestNum := Fields[0].AsInteger;
        myTestTxt := Fields[1].AsString;
      end;
      Close;
      ShowMessage('OK, only 1 record was posted to the database!');
    end;
  finally
    myPgStoredProc.Free;
  end;
end;

end.

Thx & cheers,
Peter

[ZEuS]

Thank you for the information.
We have fixed the error, and the fix will be included in the next PgDAC build.
For now, you can open the MemDS.pas unit in the editor, then find the TMemDataSet.CreateFields method implementation, and change it as follows:

Code: Select all

{$IFDEF FPC}
const
 ObjectFieldTypes = [ftADT, ftArray, ftReference, ftDataSet];

procedure TMemDataSet.CreateFields;
var
 Field: TField;
 i: longint;
begin
 for i := 0 to FieldDefs.Count - 1 do
   with FieldDefs[i] do begin
     if (DataType <> ftUnknown) and not (DataType in ObjectFieldTypes) and
       not ((faHiddenCol in Attributes) and not FieldDefs.HiddenFields) then
       Field := CreateField(Self);

     if ((faFixed in Attributes) or (DataType = ftFixedChar)) and (Field <> nil) and (Field is TStringField) then
       TStringField(Field).FixedChar := True;
   end;
end;
{$ENDIF}