Postgresql out argument for parameters in functions

[SiegfriedN]

Hi,

How does one get the value returned into a Postgresql 'out' function parameter with Zeoslib?

For example.

Postgres function

'create or replace function test1(in varchar, in char, out varchar);
..etc

When calling the function from a TZQuery

SQL.Text := 'select test1(:par1, :par2)';
..etc

This works great, but how does one get the out parameter value?

I tried to add the out parameter like this before ExecSQL

qry.Params.CreateParam('ouputfld', ftString, ptOutput);

However the 'outfld' param does not contain the value passed from the server after the ExecSQL; (just '')

If this is possible to do this with Zeos? I really hope so.
I am using version 6.6.1 at the moment.

Thanks

Siegfried

[SiegfriedN]

Hi,

Postgres returns out parameters as a resultset thus the following returns the out parameter values from the server.

Instead of calling ExecSQL use the Open method of TZQuery.

All the out parameters are then returned in one field seperated by commas. i.e. query.Fields[0].Value
e.g. '('text1',1234)'

One then has to parse out the comma seperated different 'out' parameters.

Great, Go ZeosLib!

Siegfried

[btrewern]

I'm not sure you should need to parse the result set. It should come back as a proper recordset. I'm currently using 6.6.2rc and functions similar to the following:

Code: Select all

CREATE type test_tp as
  (a integer,
   b text);

CREATE FUNCTION test_fn(code integer) RETURNS SETOF test_tp AS
$$
  SELECT
    a, b
  FROM
    test_table
  WHERE
    code = $1
$$
LANGUAGE 'sql';

then do in a ZQuery

Code: Select all

SELECT * FROM test_fn(:CODE);

This produces a proper recordset and can be used like stored procedures.

I wonder if you did

Code: Select all

select * FROM test1(:par1, :par2);

you might get better results.

Regards,

Ben