--> 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>;
end;
--> 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?
