Thread: 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>;
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?
--> 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?
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@aklaver.com
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@aklaver.com
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@aklaver.com