views.sql

-- normal view
create or replace view v_test as select a, b from t_test;
explain select * from v_test where a < 100;

-- materialized view
create materialized view m_test as select a, b from t_test;
refresh materialized view m_test ; -- locking version
refresh materialized view CONCURRENTLY m_test ; -- needs unique key!

-- recursion
create recursive view v_rec(n) as
select 1
union all
select n+1 from v_rec where n < 100;

-- organigam example
create table t_emp(id serial not null, manager_id int);
insert into t_emp(manager_id) select generate_series(1, 3);
insert into t_emp(manager_id) select 4;

create or replace recursive view v_all(id) as
select id from t_emp where manager_id is null
union all
select t.id from t_emp t where manager_id = id;

create or replace recursive view v_managers(id) as
select manager_id from t_emp where id = 4
union all
select t.manager_id from t_emp t join v_manager v on t.id = v.id;