From: | RAJIN RAJ K <rajin89(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Alternate methods for multiple rows input/output to a function. |
Date: | 2019-05-28 14:36:16 |
Message-ID: | CAC+XFJgGBkHUrypj0m=kTmQJBYBh6NcfL1qYaaSvQKw73mrijw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers pgsql-sql |
--> 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-05-28 14:59:30 | Re: Alternate methods for multiple rows input/output to a function. |
Previous Message | Adrian Klaver | 2019-05-28 13:53:32 | Re: psql: FATAL: the database system is starting up |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-05-28 14:59:30 | Re: Alternate methods for multiple rows input/output to a function. |
Previous Message | David Fetter | 2019-05-28 12:40:27 | Re: Add command column to pg_stat_progress_create_index |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-05-28 14:59:30 | Re: Alternate methods for multiple rows input/output to a function. |
Previous Message | Mohamed DIA | 2019-05-23 10:09:52 | Re: Create function using quote_literal issues |