[Gnumed-devel] some comm convenience views

[sjtan]

I was  looking for readonly view like v_home_address and v_basic_person  
for comms but couldn't find one ,
so I did a 2 stage view. v_linked_comms lists all comm types against 
each identity, and puts in blanks if no comm_channel exists
for that type and identity.  v_flat_comms uses v_linked_comms to put all 
the urls for each identity under respective field names.
I tried to do it using left joins , but this produced illegal rows.

If it's acceptable, I can put it in demographics_views.sql script.

One advantage of using such a view is that 5 sql accesses for 5 comm 
types for one identity is reduced to one sql access.





create view v_linked_comm as select * from  (select i.id as id, e.id as id_type 
from (enum_comm_types e cross join identity i )) as ic left join ( select  
l.id_identity as id , c.url as url ,c.id_type from lnk_identity2comm_chan l , 
comm_channel c where l.id_comm = c.id ) as l_comm  using (id, id_type) order by 
id;

create view flat_comms as select distinct on (id) v1.id as id, v1.url as email 
, v2.url as fax, v3.url as homephone , v4.url as workphone , v5.url as mobile 
from v_linked_comm v1 , v_linked_comm v2  ,v_linked_comm v3 , v_linked_comm v4, 
v_linked_comm v5 where v1.id = v2.id and v2.id = v3.id and v3.id = v4.id  and 
v4.id = v5.id and v1.id_type = 1 and v2.id_type = 2 and v3.id_type = 3 and 
v4.id_type=4 and v5.id_type = 5;