Select from PostgreSQL function that returns composite type
Postgres 9.3 or later
Use a LATERAL
join!
SELECT p.id, p.name, p.data, f.*FROM tb_projects pLEFT JOIN LATERAL fn_getlinkedproject(p.id) f(linked_id, lined_name) ON TRUE;
Result:
id | data | name | linked_id | linked_name----+--------+--------+-----------+------------- 1 | data_1 | name_1 | 2 | name_2 2 | data_2 | name_2 | 3 | name_3 3 | data_3 | name_3 | 1 | name_1
See:
Postgres 9.2 or older
Inferior for several reasons. Attaching column aliases is not as simple. Rather rename other conflicting names:
SELECT p.id AS p_id, p.data AS p_data, p.name AS p_name, (fn_getlinkedproject(p.id)).*FROM tb_projects p;
Result:
p_id | p_data | p_name | id | name------+--------+--------+----+-------- 1 | data_1 | name_1 | 2 | name_2 2 | data_2 | name_2 | 3 | name_3 3 | data_3 | name_3 | 1 | name_1
To rename the result columns, you have to:
SELECT p.id, p.data, p.name ,(fn_getlinkedproject(p.id)).id AS linked_id ,(fn_getlinkedproject(p.id)).name AS linked_nameFROM tb_projects p;
Both old solutions resolve to the same (poor) query plan of calling the function repeatedly.
To avoid that, use a subquery:
SELECT p.id, p.data, p.name , (p.x).id AS linked_id, (p.x).name AS linked_nameFROM (SELECT *, fn_getlinkedproject(id) AS x FROM tb_projects) p;
Note the placement of essential parentheses.
Read the manual about composite types.
Demo
CREATE TYPE dm_nameid AS ( id int, name text); -- types simplified for demoCREATE TABLE tb_projects( id int, data text, name text);INSERT INTO tb_projects VALUES (1, 'data_1', 'name_1'), (2, 'data_2', 'name_2'), (3, 'data_3', 'name_3');CREATE function fn_getlinkedproject(integer) -- avoiding CaMeL-case for demo RETURNS dm_nameid LANGUAGE sql AS'SELECT id, name FROM tb_projects WHERE id = ($1 % 3) + 1';
db<>fiddle here