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

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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com