I am writing an sql program that creates a table with columns computed in a function. The function returns the record type.
This is what the header looks like:
create or replace function get_items(col1 int, col2 int) returns record
What I would like to be able to do is:
create table table_items as (
with q as (
select *,
(SELECT * FROM get_items(t.col1, t.col2) AS (item1 integer, item2 integer))
from
table_t as t
)
select * from q
);
however, that results in:
ERROR: subquery must return only one column
To fix the error I changed the code into:
create table table_items as (
with q as (
select *,
(SELECT item1 FROM get_items(t.col1, t.col2) AS (item1 integer, item2 integer)),
(SELECT item2 FROM get_items(t.col1, t.col2) AS (item1 integer, item2 integer))
from
table_t as t
)
select * from q
);
This solution works, though twice as slow when item2 is obtained in addition to item1.
I assume that it is because the same query is executed twice.
Is there a way to obtain both items having the function invoked only once?
Thanks a lot!
>Solution :
This is a good use case for a lateral join.
create table table_items as
select *
from table_t as t
cross join lateral
get_items(t.col1, t.col2) as l(item1 integer, item2 integer);