How to SELECT a PROCEDURE in Firebird 2.5

Question

I'm using Firebird Embedded v2.5. How to use procedures in query (SELECT) ?

My procedure:

SET TERM ^ ;
CREATE PROCEDURE FN_TEST( Y INTEGER )
RETURNS( X INTEGER)
AS
BEGIN
X = Y + 1;
END^
SET TERM ; ^

I want to list some field of table modified by some procedure, like this:

SELECT some_table_field_1, 
       fn_test( 4 ) AS zzz, 
       some_table_field_2,
       fn_test( some_table_field_2 ) AS field_2_modified
FROM   tb_test

Need results (table):

some_table_field_1     zzz         some_table_field_2      field_2_modified
---------------------------------------------------------------------------
aaa                    5           14                      15
bbb                    5           23                      24
www                    5           75                      76

This thing works fine in PostgreSQL, but I don't know how to do this in Firebird.

Answer 1

SELECT some_table_field_1, 
   (select X from fn_test( 4 )) AS zzz, 
   some_table_field_2,
   (select X from fn_test( some_table_field_2 )) AS field_2_modified
FROM   tb_test

Answer 2

FN_TEST is an executable procedure: it can be called via the EXECUTE PROCEDURE statement and it returns a single set of output parameters.

In Firebird 2.x only a selectable stored procedure can be "used" as a view / table (see Firebird Stored Procedures).

So:

SELECT FN_TEST(some_table_field) AS field_modified
FROM   tb_test

produces an invalid request BLR at offset... error.

You could change your procedure as suggested but, actually, the feature you need has been introduced in Firebird 3 in the form of stored function:

CREATE FUNCTION FN_TEST(Y INT) RETURNS INT
AS
BEGIN
  RETURN Y + 1;
END;

SELECT FN_TEST(4) AS zzz
FROM   tb_test

Further details in Functions with PSQL in Firebird 3.