CREATE or REPLACE VIEW titan.epn_core AS SELECT 
text 'granule' AS resource_type,  
text '1' AS dataset_id,  
id AS index,
text 'pr' AS dataproduct_type, 
text 'Titan' AS target_name, 
text 'satellite' AS target_class, 
cast(to_char(date::date,'J') as double precision) AS time_min,
cast(to_char(date::date,'J') as double precision) AS time_max,
CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_min,
CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_min,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_range_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_range_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_max,
longitude AS c1min,
longitude AS c1max,
latitude AS c2min,
latitude AS c2max,
CAST(NULL AS DOUBLE PRECISION) as c3min,
CAST(NULL AS DOUBLE PRECISION) as c3max,
CAST(NULL AS DOUBLE PRECISION) as c1_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c1_resol_max,
CAST(NULL AS DOUBLE PRECISION) as c2_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c2_resol_max,
CAST(NULL AS DOUBLE PRECISION) as c3_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c3_resol_max,
text 'body' AS spatial_frame_type,
CAST(NULL AS DOUBLE PRECISION) as incidence_min,
CAST(NULL AS DOUBLE PRECISION) as incidence_max,
CAST(NULL AS DOUBLE PRECISION) as emergence_min,
CAST(NULL AS DOUBLE PRECISION) as emergence_max,
CAST(NULL AS DOUBLE PRECISION) as phase_min,
CAST(NULL AS DOUBLE PRECISION) as phase_max,
text 'cassini' AS instrument_host_name,
text 'Composite Infrared Spectrometer (CIRS)' AS instrument_name, 
text 'phys.pressure phys.temperature phys.abund' AS measurement_type,
'http://voparis-srv.obspm.fr/srv/data/titan_abundance.php?id='||id||'&format=votable' AS access_url,
text 'VOTable' AS access_format,
integer '143' AS access_estsize,
integer '5' AS processing_level,
text 'vo paris data centre on behalf of LESIA' AS publisher,
text 'http://adsabs.harvard.edu/abs/2010Icar..205..559V' AS reference,
text 'abundance Vertical Profile at Titan with Cassini/CIRS' AS title,
text 'atmosphere' AS target_region,
element AS species,
text  'UTC' AS time_scale 
FROM titan.profq
UNION ALL
SELECT
text 'dataset' AS resource_type,  
text '1' AS dataset_id,  
INTEGER '1' AS index,
text 'pr' AS dataproduct_type,
text 'Titan' AS target_name, 
text 'satellite' AS target_class, 
min(cast(to_char(date::date,'J') as double precision)) AS time_min,
max(cast(to_char(date::date,'J') as double precision)) AS time_max,
CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_min,
CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_min,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_range_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_range_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_max,
min(latitude) AS c1min,
max(latitude) AS c1max,
min(longitude) AS c2min,
max(longitude) AS c2max,
CAST(NULL AS DOUBLE PRECISION) as c3min,
CAST(NULL AS DOUBLE PRECISION) as c3max,
CAST(NULL AS DOUBLE PRECISION) as c1_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c1_resol_max,
CAST(NULL AS DOUBLE PRECISION) as c2_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c2_resol_max,
CAST(NULL AS DOUBLE PRECISION) as c3_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c3_resol_max,
text 'body' AS spatial_frame_type,
CAST(NULL AS DOUBLE PRECISION) as incidence_min,
CAST(NULL AS DOUBLE PRECISION) as incidence_max,
CAST(NULL AS DOUBLE PRECISION) as emergence_min,
CAST(NULL AS DOUBLE PRECISION) as emergence_max,
CAST(NULL AS DOUBLE PRECISION) as phase_min,
CAST(NULL AS DOUBLE PRECISION) as phase_max,
text 'cassini' AS instrument_host_name,
text 'Composite Infrared Spectrometer (CIRS)' AS instrument_name,
text 'phys.pressure phys.temperature phys.abund' AS measurement_type,
NULL AS access_url,
text 'VOTable' AS access_format,
NULL AS access_estsize,
integer '5' AS processing_level,
text 'vo paris data centre on behalf of LESIA' AS publisher,
text 'http://adsabs.harvard.edu/abs/2010Icar..205..559V' AS reference,
text 'abundance Vertical Profile at Titan with Cassini/CIRS' AS title,
text 'atmsphere' AS target_region,
array_to_string(array(select distinct element from titan.profq),' ') AS species,
text  'UTC' AS time_scale 
FROM titan.profq
UNION ALL
SELECT 
text 'granule' AS resource_type,  
text '2' AS dataset_id,  
id AS index,
text 'pr' AS dataproduct_type, 
text 'Titan' AS target_name, 
text 'satellite' AS target_class, 
cast(to_char(date::date,'J') as double precision) AS time_min,
cast(to_char(date::date,'J') as double precision) AS time_max,
CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_min,
CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_min,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_range_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_range_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_max,
longitude AS c1min,
longitude AS c1max,
latitude AS c2min,
latitude AS c2max,
CAST(NULL AS DOUBLE PRECISION) as c3min,
CAST(NULL AS DOUBLE PRECISION) as c3max,
CAST(NULL AS DOUBLE PRECISION) as c1_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c1_resol_max,
CAST(NULL AS DOUBLE PRECISION) as c2_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c2_resol_max,
CAST(NULL AS DOUBLE PRECISION) as c3_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c3_resol_max,
text 'body' AS spatial_frame_type,
CAST(NULL AS DOUBLE PRECISION) as incidence_min,
CAST(NULL AS DOUBLE PRECISION) as incidence_max,
CAST(NULL AS DOUBLE PRECISION) as emergence_min,
CAST(NULL AS DOUBLE PRECISION) as emergence_max,
CAST(NULL AS DOUBLE PRECISION) as phase_min,
CAST(NULL AS DOUBLE PRECISION) as phase_max,
text 'cassini' AS instrument_host_name,
text 'Composite Infrared Spectrometer (CIRS)' AS instrument_name, 
text 'phys.pressure phys.temperature' AS measurement_type,
'http://voparis-srv.obspm.fr/srv/data/titan_temperature.php?id='||id||'&format=votable' AS access_url,
text 'VOTable' AS access_format,
integer '8' AS access_estsize,
integer '5' AS processing_level,
text 'vo paris data centre on behalf of LESIA' AS publisher,
text 'http://adsabs.harvard.edu/abs/2010Icar..205..559V' AS reference,
text 'abundance Vertical Profile at Titan with Cassini/CIRS' AS title,
text 'atmsphere' AS target_region,
NULL AS species,
text  'UTC' AS time_scale 
FROM titan.proft
UNION ALL
SELECT 
text 'dataset' AS  resource_type,  
text '2' AS dataset_id,  
INTEGER '2' AS index,
text 'pr' AS dataproduct_type,
text 'Titan' AS target_name, 
text 'satellite' AS target_class, 
min(cast(to_char(date::date,'J') as double precision)) AS time_min,
max(cast(to_char(date::date,'J') as double precision)) AS time_max,
CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_min,
CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_min,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_range_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_range_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_min,
CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_max,
min(latitude) AS c1min,
max(latitude) AS c1max,
min(longitude) AS c2min,
max(longitude) AS c2max,
CAST(NULL AS DOUBLE PRECISION) as c3min,
CAST(NULL AS DOUBLE PRECISION) as c3max,
CAST(NULL AS DOUBLE PRECISION) as c1_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c1_resol_max,
CAST(NULL AS DOUBLE PRECISION) as c2_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c2_resol_max,
CAST(NULL AS DOUBLE PRECISION) as c3_resol_min,
CAST(NULL AS DOUBLE PRECISION) as c3_resol_max,
text 'body' AS spatial_frame_type,
CAST(NULL AS DOUBLE PRECISION) as incidence_min,
CAST(NULL AS DOUBLE PRECISION) as incidence_max,
CAST(NULL AS DOUBLE PRECISION) as emergence_min,
CAST(NULL AS DOUBLE PRECISION) as emergence_max,
CAST(NULL AS DOUBLE PRECISION) as phase_min,
CAST(NULL AS DOUBLE PRECISION) as phase_max,
text 'cassini' AS instrument_host_name,
text 'Composite Infrared Spectrometer (CIRS)' AS instrument_name, 
text 'phys.pressure phys.temperature' AS measurement_type,
NULL AS access_url,
text 'VOTable' AS access_format,
NULL AS access_estsize,
integer '5' AS processing_level,
text 'vo paris data centre on behalf of LESIA' AS publisher,
text 'http://adsabs.harvard.edu/abs/2010Icar..205..559V' AS reference,
text 'abundance Vertical Profile at Titan with Cassini/CIRS' AS title,
text 'atmsphere' AS target_region,
NULL AS species,
text  'UTC' AS time_scale 
FROM titan.proft;

GRANT ALL PRIVILEGES ON SCHEMA titan TO gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA titan TO gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON titan.proft to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON titan.proft to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON titan.profq to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON titan.profq to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON titan.epn_core to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON titan.epn_core to gavo WITH GRANT OPTION;
