I have a simple user defined function as following:
create or replace function test()
returns table (a float, b float) as
$$
begin
drop table if exists test1;
create table test1(a float, b float);
insert into test1 values(1,1),(2,1),(3,3);
return query select * from test1;
end;
$$ language plpgsql;
I have another user defined function. In this one, I want to call the function above.
create or replace function test2()
returns table (a float) as
$$
begin
select test();
return query select a+b from test1;
end;
$$language plpgsql;
after this, when I do:
select test2();
It gives me an error:
query has no destination for result data.
If I make my second function as the following:
create or replace function test2()
returns table (a float) as
$$
select a+b from test();
$$language sql;
Then I do select test2();
It runs correctly. I think the reason why this doesn't work is when I return something from sql function, it returns a table, but when I return something from a plpgsql function, it returns something look like a tuple. For example (1,2). 1 will be the first attribute of the table, 2 will be the second one. This is just a simple example, I can use sql instead of plpgsql to solve this problem. However, in my real project, I really want to call a plpgsql function in another plpgsql function.
Then main structure of my program will look like the following:
plpgsql function 1
plpgsql function 2
plpgsql function 3
In my main plpgsql function, I want to call all these three functions. Does anyone have a idea of how to do that?
