Guanyan Lin Guanyan Lin - 1 month ago 9
SQL Question

In Plpgsql, how to use user defined function in another user defined function

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?

Answer

Like the error message says:

query has no destination for result data.

Meaning, you cannot execute a SELECT in a plpgsql function without destination for the result.
Use PERFORM instead of SELECT if you want to discard the result.

Related:

Comments