By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TYPE dm_nameid AS (
id int
, name text -- types simplified for demo
);
CREATE 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';
3 rows affected
-- Postgres 9.3+
SELECT p.id, p.data, p.name, f.*
FROM tb_projects p
LEFT JOIN LATERAL fn_getlinkedproject(p.id) f(linked_id, lined_name) ON TRUE;
| id | data | name | linked_id | lined_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 |
-- Postgres 9.2-
SELECT p.id AS p_id, p.data AS p_data, p.name AS p_name,(fn_getlinkedproject(p.id)).*
FROM tb_projects p;
| 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 |
-- Postgres 9.2-, with column aliases
SELECT p.id, p.data, p.name
,(fn_getlinkedproject(p.id)).id AS linked_id
,(fn_getlinkedproject(p.id)).name AS linked_name
FROM tb_projects p;
| 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 |