2

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?

Zazimba
  • 23
  • 3
  • As far as I know, Firebird now supports *scalar* functions, not table-valued functions: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-psql-funcs.html. – Gordon Linoff Oct 14 '20 at 10:38
  • @GordonLinoff Firebird has selectable stored procedures, which are somewhat like table-valued functions. – Mark Rotteveel Oct 14 '20 at 12:19

1 Answers1

4

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)
Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158