Can't call Postgresql function which should return text value

Question

I have a little problem when calling a Postgresql function in Delphi with FireDAC. The Postgresql function has the following definition:

CREATE OR REPLACE FUNCTION public."pgpDecryptMe" (
  todecode text
)
RETURNS text AS
$body$
DECLARE
  PGPPrivate TEXT;  
BEGIN
...

So it expects a "text" value and returns a "text" value. I can call it with a long text parameter (over 900 character) and it returns the correct value in any sql admin tool without any problems.

select "pgpDecryptMe"('c1c04c030...a378624e6a659a20765') as Decrypt

But calling it in Delphi with the following code:

PGQuery.SQL.Text := 'select "pgpDecryptMe"(:test) as testvalue';
PGQuery.ParamByName('test').AsString := 'c1c04c030...a378624e6a659a20765';
PGQuery.Open();

Gives me the following error message:

[FireDAC][DatS]-2. Object [id] is not found

I googled and searched here but can't find any solution for the problem. It is probably something very small I can't see :-(

I am working with Delphi XE7 and PostgreSQL 9.3

Answer 1

Ok, now I got it working. It looks like it needed an additional index field name which doesn't really make sense because it just returned one value...

So it worked when I changed my code to the following:

PGQuery.SQL.Text := 'select "pgpDecryptMe"(:test) as testvalue';
PGQuery.ParamByName('test').AsString := 'c1c04c030...a378624e6a659a20765';
PGQuery.IndexFieldNames := 'testvalue';
PGQuery.Open();