How to SELECT a PROCEDURE in Firebird 2.5

问题:

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.

回答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

回答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.

回答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)

回答4:

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

回答5:

Try this

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

回答6:

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.

回答7:

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

回答8:

You can use EXECUTE BLOCK Please have a look EXECUTE BLOCK