Re: Alternate methods for multiple rows input/output to a function. - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Alternate methods for multiple rows input/output to a function. |
Date | |
Msg-id | 6e492dc3-c1dd-2a97-643d-411cc0655fd6@aklaver.com Whole thread Raw |
List | pgsql-general |
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
pgsql-general by date: