I have a function my_funct(param1,param2) returning a SELECT QUERY as a text, I usually C/c the result, remove quotes, then run it.
How can execute the returned text in a query that call the function ?
I expect something like this:
SELECT resultOf( myfunct('foo','bar'))
with no extra columnname/type to declare. If such function does not exits built-in, let's create it, I don't mind.
If I understood correctly you want
1. Using Do Block:
DO
$$
declare
ref_cursor refcursor:='mycursor';
begin
open ref_cursor for execute (select * from my_funct('foo','bar')) ;
end;
$$;
--Fetch all data from cursor
Fetch all from mycursor;
2. Using Function returning refcursor:
You can create a function like below:
create function my_funct1(param1 text) returns refcursor as
$$
declare
ref_cursor refcursor:='mycursor';
begin
open ref_cursor for execute param1;
return ref_cursor;
end;
$$
language plpgsql
To call above function use following code:
begin ;
select my_funct1((select * from my_funct('foo','bar')) );
fetch all from mycursor;
commit;