On 5/28/19 7:36 AM, RAJIN RAJ K wrote:
--> 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)
To be honest I cannot follow what you are trying to achieve below. I do
have one suggestion as to creating temp tables.
Why not use a CTE:
https://www.postgresql.org/docs/11/queries-with.html
in the function to build a 'temp' table on the fly?
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 <http://a.id> = b.id <http://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?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
