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 TABLE tbl (student_id int, site text, start_date date, end_date date, primary_or_secondary text);
INSERT INTO tbl VALUES
(1, 'A', '2019-01-01', '2019-02-28', 'Primary')
, (1, 'B', '2019-02-01', '2019-06-30', 'Secondary')
, (1, 'C', '2019-03-01', '2019-06-30', 'Primary')
, (1, 'D', '2020-03-01', '2020-06-30', 'Primary') -- extended test case
, (1, 'E', '2020-03-01', '2020-04-01', 'Secondary')
, (1, 'F', '2020-04-04', '2020-05-01', 'Secondary')
, (1, 'G', '2020-05-02', '2020-06-30', 'Secondary')
, (1, 'H', '2020-07-01', '2020-07-31', 'Primary')
, (1, 'I', '2020-08-01', '2020-08-31', 'Secondary')
, (3, 'C', '2019-03-01', '2019-06-30', 'Primary') -- more users
, (2, 'A', '2019-01-01', '2019-02-28', 'Primary')
, (3, 'B', '2019-02-01', '2019-06-30', 'Secondary')
, (2, 'C', '2019-03-01', '2019-06-30', 'Primary')
, (3, 'A', '2019-01-01', '2019-02-28', 'Primary')
, (2, 'B', '2019-02-01', '2019-06-30', 'Secondary')
, (4, 'B', '2019-02-01', '2019-06-30', 'Secondary')
;
16 rows affected
CREATE OR REPLACE FUNCTION f_student_xtab(_student_ids int[])
RETURNS TABLE (
student_id int
, "primary" text
, secondary text
, start_date date
, end_date date) AS
$func$
DECLARE
r record;
BEGIN
student_id := -1; -- init with impossible value
FOR r IN
SELECT t.student_id, t.site, t.primary_or_secondary = 'Primary' AS prim, l.range_end, l.date
FROM tbl t
CROSS JOIN LATERAL (
VALUES (false, t.start_date)
, (true , t.end_date)
) AS l(range_end, date)
WHERE t.student_id = ANY (_student_ids)
ORDER BY t.student_id, l.date, range_end -- start of range first
LOOP
IF r.student_id <> student_id THEN
student_id := r.student_id;
IF r.prim THEN "primary" := r.site;
ELSE secondary := r.site;
END IF;
start_date := r.date;
ELSIF r.range_end THEN
IF r.date < start_date THEN
-- range already reported
IF r.prim THEN "primary" := NULL;
ELSE secondary := NULL;
END IF;
start_date := NULL;
SELECT * FROM f_student_xtab('{1,2,3}');
| student_id | primary | secondary | start_date | end_date |
|---|---|---|---|---|
| 1 | A | null | 2019-01-01 | 2019-01-31 |
| 1 | A | B | 2019-02-01 | 2019-02-28 |
| 1 | C | B | 2019-03-01 | 2019-06-30 |
| 1 | D | E | 2020-03-01 | 2020-04-01 |
| 1 | D | null | 2020-04-02 | 2020-04-03 |
| 1 | D | F | 2020-04-04 | 2020-05-01 |
| 1 | D | G | 2020-05-02 | 2020-06-30 |
| 1 | H | null | 2020-07-01 | 2020-07-31 |
| 1 | null | I | 2020-08-01 | 2020-08-31 |
| 2 | A | null | 2019-01-01 | 2019-01-31 |
| 2 | A | B | 2019-02-01 | 2019-02-28 |
| 2 | C | B | 2019-03-01 | 2019-06-30 |
| 3 | A | null | 2019-01-01 | 2019-01-31 |
| 3 | A | B | 2019-02-01 | 2019-02-28 |
| 3 | C | B | 2019-03-01 | 2019-06-30 |