CREATE or REPLACE VIEW radiojove.epn_core AS SELECT 
text 'granule' AS resource_type,  
text '1' AS dataset_id,
id   AS index,
dataproduct_type AS dataproduct_type, 
target_name  AS target_name, 
target_class  AS target_class, 
time_min AS time_min,
time_max AS time_max,
time_sampling_step_min AS time_sampling_step_min,
time_sampling_step_max AS time_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_min,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_max,
spectral_range_min AS spectral_range_min,
spectral_range_max 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,
CAST(NULL AS DOUBLE PRECISION) AS c1min,
CAST(NULL AS DOUBLE PRECISION) AS c1max,
CAST(NULL AS DOUBLE PRECISION) AS c2min,
CAST(NULL AS DOUBLE PRECISION) 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,
CAST(NULL AS TEXT)  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,
instrument_host_name AS instrument_host_name,
instrument_name AS instrument_name, 
measurement_type AS measurement_type,
access_url  AS access_url,
access_format AS access_format,
size AS access_estsize,
integer '3' AS processing_level,
target_region AS target_region,
target_element AS target_element,
instrument_type AS instrument_type,
observer_name AS observer_name,
observatory_location AS observatory_location,
observatory_longitude AS observatory_longitude,
observatory_latitude AS observatory_latitude,
n_channel AS n_channel,
dipole_orientation_angle AS dipole_orientation_angle,
preview_url AS preview_url,
thumbnail_url AS thumbnail_url
-- FROM dblink('dbname=postgres host=127.0.0.1 user=cakephp password=toto','SELECT 
FROM dblink('dbname=radiojove host=voplus.obspm.fr user=epn_user password=epn_user','SELECT 
f.id, f.size, f.dataproduct_type, f.target_name, f.target_class, f.target_region, f.target_element, f.time_min, f.time_max, f.time_sampling_step_min, f.time_sampling_step_max, f.spectral_range_min, f.spectral_range_max, f.instrument_host_name, f.instrument_name, f.instrument_type, f.measurement_type, f.access_format, f.observer_name, f.observatory_location, f.observatory_longitude, f.observatory_latitude, f.access_url, f.n_channel, f.dipole_orientation_angle, f.preview_url, f.thumbnail_url
FROM files as f, filestatuses where filestatuses.id=f.filestatus_id and filestatuses.name=''validated'' ') AS mytab(
"id" integer,
"size" integer,
"dataproduct_type" varchar(200),
"target_name" varchar(200),
"target_class" varchar(200),
"target_region" varchar(200),
"target_element" varchar(200),
"time_min" numeric(17,10),
"time_max" numeric(17,10),
"time_sampling_step_min" double precision,
"time_sampling_step_max" double precision,
"spectral_range_min" double precision,
"spectral_range_max" double precision,
"instrument_host_name" varchar(200),
"instrument_name" varchar(200),
"instrument_type" varchar(200),
"measurement_type" varchar(200),
"access_format" varchar(200),
"observer_name" varchar(200),
"observatory_location" varchar(200),
"observatory_longitude" double precision,
"observatory_latitude" double precision,
"access_url" varchar(400),
"n_channel" integer,
"dipole_orientation_angle" varchar(200),
"preview_url" varchar(400),
"thumbnail_url" varchar(400)
);



GRANT ALL PRIVILEGES ON SCHEMA radiojove TO gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA radiojove TO gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA radiojove TO dachsroot WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON radiojove.epn_core to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON radiojove.epn_core to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON radiojove.epn_core to dachsroot WITH GRANT OPTION;



