Returning a table in firebird 3.0 with stored function or stored procedure

Question

I'm trying to write a stored procedure/function that returns me a table with one or multiple rows of data.

The returned data depends on a variable shown in the following sql statement:

  SELECT * FROM table_name AS SD WHERE EXISTS
  (SELECT DISTINCT S.PARENT_ID FROM table_name AS S 
  WHERE S.COMPONENT_ID = 10011 AND S.CARRIER_GROUP_ID = X AND SD.SD_ID = S.PARENT_ID)

So far I have seen that something is done like this:

CREATE FUNCTION f_test_function (X INT)
RETURNS TABLE
AS
RETURN
   (SELECT * FROM table_name AS SD WHERE EXISTS
      (SELECT DISTINCT S.PARENT_ID FROM table_name AS S 
      WHERE S.COMPONENT_ID = 10011 AND S.CARRIER_GROUP_ID = X AND SD.SD_ID = S.PARENT_ID));

Afterwards you call the function/procedure with a X value. I know that there is something wrong with the returns type but I don't know what.

Can anyone help?

Answer 1

What you are looking for is a selectable stored procedure. Firebird requires you to explicitly declare the columns the stored procedure returns, so something like returns table is not an option. For example:

create procedure sp_test_procedure (x integer) 
  returns (column1 integer, column2 varchar(50))
as
begin
  for select value1, value2 
      from table_name SD
      where exists (
        SELECT DISTINCT S.PARENT_ID 
        FROM table_name AS S 
        WHERE S.COMPONENT_ID = 10011 
        AND S.CARRIER_GROUP_ID = :X 
        AND SD.SD_ID = S.PARENT_ID)
      into column1, column2
  do
  begin
    suspend;
  end
end

You will need to explicitly map the columns, so a simple select * is not a good idea.

Note the use of for select, which selects zero or more rows and iterates over the cursor, and suspend, which outputs a row to be fetched from the stored procedure (in this case for each row of the cursor).

You can produce values from this procedure like:

select column1, column2
from sp_test_procedure(10)