agora inbox for pgsql-sql@postgresql.orghelp / color / mirror / Atom feed
Alternate methods for multiple rows input/output to a function. 3+ messages / 2 participants [nested] [flat]
* Alternate methods for multiple rows input/output to a function. @ 2019-05-28 14:36 RAJIN RAJ K <rajin89@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: RAJIN RAJ K @ 2019-05-28 14:36 UTC (permalink / raw) To: pgsql-sql; pgsql-hackers@postgresql.org; pgsql-general@postgresql.org --> 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? ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Alternate methods for multiple rows input/output to a function. @ 2019-05-28 14:59 Adrian Klaver <adrian.klaver@aklaver.com> parent: RAJIN RAJ K <rajin89@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Adrian Klaver @ 2019-05-28 14:59 UTC (permalink / raw) To: RAJIN RAJ K <rajin89@gmail.com>; pgsql-sql; pgsql-hackers@postgresql.org; pgsql-general@postgresql.org 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Alternate methods for multiple rows input/output to a function. @ 2019-05-28 17:26 Adrian Klaver <adrian.klaver@aklaver.com> parent: Adrian Klaver <adrian.klaver@aklaver.com> 0 siblings, 0 replies; 3+ messages in thread From: Adrian Klaver @ 2019-05-28 17:26 UTC (permalink / raw) To: RAJIN RAJ K <rajin89@gmail.com>; +Cc: pgsql-general <pgsql-general@postgresql.org> On 5/28/19 8:06 AM, RAJIN RAJ K wrote: Please reply to list also. Ccing list. > Thanks for the response. > > CTE is not useful in my case. Here i want to pass the table to a > function and get the filtered results back from the function. > I tried few but not use full. > 1. Pass table input --> Ref cursor is the only option but which again > require loop to fetch the records. (FETCH ALL results cannot be stored > in a variable) > Here im creating temp table withe required input data before the > function call. I'm going to take a stab at this though I do not entirely follow the logic. Definitely not tested: 1) create function filter_id(tbl_name varchar) return table (id bigint) begin --> Assuming input table is already created #temp_input_id return query EXECUTE format('select id ' 'from tbl a ' 'inner join' '%I b on (a.id = b.id)' 'where a.<conditions>', tbl_name); end; 2) In calling function: WITH temp_tbl AS (select id from tbla... ), filter_tbl AS (select * from filter_id(temp_bl)) select a.* from tb3 a inner join tb4 inner join tb 5 inner join filter_tbl; > > > On Tue, May 28, 2019 at 8:29 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > 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; <http://a.id; = b.id > <http://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 <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-05-28 17:26 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-05-28 14:36 Alternate methods for multiple rows input/output to a function. RAJIN RAJ K <rajin89@gmail.com> 2019-05-28 14:59 ` Adrian Klaver <adrian.klaver@aklaver.com> 2019-05-28 17:26 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox