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.

Unflinching

What exactly is your problem? Do you get an error message? If yes, post it – Kiernan

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

Microspore

fn_test is not selectable (it's an executable procedure). The proposed solution produces an invalid request BLR at offset... error. – Elianore

Add a SUSPEND; statement just before the END^ statement and the function becomes selectable. – Shamrock

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.

Elianore

Answer 3

Try

SELECT some_table_field_1, 
   fn_test.x AS zzz, 
   some_table_field_2,
FROM   tb_test
LEFT JOIN fn_test(some_table_field_1) ON (1 = 1)

Tensor

fn_test is not selectable (it's an executable procedure). The proposed solution produces an invalid request BLR at offset... error. – Elianore

Answer 4

You can't call stored procedures in Firebird's select list. You have to write the selectable procedure with desire result or write an UDF function to do what you have in fn_test procedure.

For your case the simplest way is:

SELECT some_table_field_1, 
       5 AS zzz, 
       some_table_field_2,
       ( some_table_field_2 + 1) AS field_2_modified
FROM   tb_test

Marital

Answer 5

As JustMe said, you can't call stored procedures in a select. You can call stored procedure only in the FROM section. Another solution for your problem is to create a selectable procedure like this:

create or alter procedure myproc (
 n integer)
returns (
 field_1 integer,
 zzz integer,
 field_2 integer,
 modified_field_2 integer)
as
declare variable i integer;
begin
  for
    select some_table_field_1, :n+1 as zzz, some_table_field_2, (some_table_field_2+1) as field_2_modified
    from tb_test
    into :field_1, :zzz, :field_2, :modified_field_2
  do begin
    suspend;
  end
end

After run that code, you can simply query select * from myproc(4) and get what you want.

Hydrograph

Answer 6

Use UDF in order to manage calculation on fields. Stored procedure are admited only in the FROM Clause.

Cassimere

Answer 7

Try this

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

Blandish

Answer 8

You can use EXECUTE BLOCK Please have a look EXECUTE BLOCK

Knobkerrie