Alternate methods for multiple rows input/output to a function.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Alternate methods for multiple rows input/output to a function.

RAJIN RAJ K
--> 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?