Test

System

Testing

create table patients(patient_id int, pname varchar(50), visit_id int, year int);
create table visits(patient_id int, visit varchar(50), visit_id int, year int);
create table treatments(patient_id int, treatment varchar(50), visit_id int, year int);

insert into patients values(1,'Adam',10,2021);
insert into visits values(1,'my visit x',10,2021);
insert into treatments values(1,'my treatment x',10,2021);
insert into patients values(2,'Mike',20,2022);
insert into visits values(2,'my visit y',20,2022);
insert into treatments values(2,'my treatment y',20,2022);
commit;
\t
\a
\o | vsql -e
SELECT 'CREATE VIEW combined AS select ' ||
(select 'patients.patient_id, ' ||
      LISTAGG (distinct table_name || '.' || column_name USING PARAMETERS max_length=10000, on_overflow='ERROR', separator=',') ||
      ' '  from columns
           where     table_schema = 'public'
                 and table_name in ('patients','visits','treatments')
                 and column_name not in ('year','patient_id','visit_id')  -- [excluding columns..]
           ) || ' ,patients.year'
|| ' FROM patients
LEFT JOIN visits on patients.patient_id = visits.patient_id
LEFT JOIN treatments on patients.visit_id = treatments.visit_id; select * from combined;'


patient_id |   treatment    | pname |   visit    | year
------------+----------------+-------+------------+------
        1 | my treatment x | Adam  | my visit x | 2021
        2 | my treatment y | Mike  | my visit y | 2022
(2 rows)