Alternate methods for multiple rows input/output to a function.
--> Function '
filter_id ' filters the ID's based on some conditions. --> Input is set of ID's. (Not directly taking the input since there is no provision to pass multiple rows to a function)
create function filter_id() return table (id bigint) begin
--> Assuming input table is already created #temp_input_id
retun query as select id from tbl a inner join #temp_input_id b on (a.id = b.id) where a.<conditions>;
--> Calling Function:
create function caller() return table (id bigint,col1 bigint, col2 bigint) begin
--> do some processing
--> Find out the input id's for filtering.
--> Create temp table for providing input for the filtering function
create temp table #TEMP1 as select id from tbla........; (Cannot move the input id logic to filter_function)
--> calling the filter function create temp table #TEMP2 as select * from filter_id(); --> This is a generic function used in many functions.
return query as select a.* from tb3 a inner join tb4 inner join tb 5 inner join #TEMP2; end;
Is there any alternate way of achieving this? Passing multiple records to a function im creating a temp table before invoking the function. For receiving an output of multiple rows i'm creating a temp table to reuse further in the code.
Can this be done using Refcursor? Is it possible to convert refcursor to a temp table and use it as normal table in query?