drop view epn_vue;
CREATE or REPLACE VIEW epn_vue AS SELECT 
cast(app_planet.id as text) AS obs_id,
CAST(app_planet.name AS TEXT) AS target_name, 
app_star.ra AS c1min,
app_star.dec AS c2min,
cast(app_planet.modified as timestamptz ) as release_date,
to_timestamp(cast(app_planet.discovered as text),'YYYY')   AS creation_date,
cast(app_planet.modified as timestamptz ) AS modification_date,
array_to_string(array(select app_molecule.name from app_molecule, app_planet2molecule where ((app_planet2molecule.planet_ID=app_planet.id) and (app_planet2molecule.molecule_ID=app_molecule.id))),'#') as species,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(array_to_string(app_planet.detection_type,'#'),'1','Radial Velocity'),'2','Pulsar') ,'3','Controversial') ,'4','Microlensing') ,'5','Imaging'),'6','Primary Transit'),'7','Astrometry'),'8','TTV'),'9','Other'),'10','Spectrum'),'11','Theoretical'),'12','Flux'),'13','Secondary Transit'),'14','IR Excess')as detection_type,
case app_planet.publication_status 
  when '1' then cast('Published in a refereed paper' as TEXT)
  when '2' then cast('Submitted to a professional journal' as TEXT)
  when '3' then cast('Announced on a professional conference' as TEXT)
  when '4' then cast('Announced on a website' as TEXT)
ELSE CAST(NULL AS TEXT) END
    as publication_status,
app_planet.mass as mass,
app_planet.mass_error[1] as mass_error_min,
case when app_planet.mass_error[2] IS NOT NULL then app_planet.mass_error[2] else app_planet.mass_error[1] end as mass_error_max,
app_planet.radius as radius,
app_planet.radius_error[1] as radius_error_min,
case when app_planet.radius_error[2] IS NOT NULL then app_planet.radius_error[2] else app_planet.radius_error[1] end as radius_error_max,
app_planet.axis as semi_major_axis,
axis_error[1] as semi_major_axis_error_min,
case when axis_error[2] IS NOT NULL then axis_error[2] else axis_error[1] end as semi_major_axis_error_max,
app_planet.period as period,
period_error[1] as period_error_min,
case when period_error[2] IS NOT NULL then period_error[2] else period_error[1] end as period_error_max,
app_planet.eccentricity as eccentricity,
eccentricity_error[1] as eccentricity_error_min,
case when eccentricity_error[2] IS NOT NULL then eccentricity_error[2] else eccentricity_error[1] end as eccentricity_error_max,
app_planet.omega as periastron,
omega_error[1] as periastron_error_min,
case when omega_error[2] IS NOT NULL then omega_error[2] else omega_error[1] end as periastron_error_max,
app_planet.tzero_tr as tzero_tr,
tzero_tr_error[1] as tzero_tr_error_min,
case when tzero_tr_error[2] IS NOT NULL then tzero_tr_error[2] else tzero_tr_error[1] end as tzero_tr_error_max,
app_planet.tzero_vr as tzero_vr,
tzero_vr_error[1] as tzero_vr_error_min,
case when tzero_vr_error[2] IS NOT NULL then tzero_vr_error[2] else tzero_vr_error[1] end as tzero_vr_error_max,
app_planet.tperi as t_peri,
tperi_error[1] as t_peri_error_min,
case when tperi_error[2] IS NOT NULL then tperi_error[2] else tperi_error[1] end as t_peri_error_max,
app_planet.tconj as t_conj,
tconj_error[1] as t_conj_error_min,
case when tconj_error[2] IS NOT NULL then tconj_error[2] else tconj_error[1] end as t_conj_error_max,
app_planet.inclination as inclination,
inclination_error[1] as inclination_error_min,
case when inclination_error[2] IS NOT NULL then inclination_error[2] else inclination_error[1] end as inclination_error_max,
app_planet.tzero_tr_sec as tzero_tr_sec,
tzero_tr_sec_error[1] as tzero_tr_sec_error_min,
case when tzero_tr_sec_error[2] IS NOT NULL then tzero_tr_sec_error[2] else tzero_tr_sec_error[1] end as tzero_tr_sec_error_max,
app_planet.lambda_angle as lambda_angle,
lambda_angle_error[1] as lambda_angle_error_min,
case when lambda_angle_error[2] IS NOT NULL then lambda_angle_error[2] else lambda_angle_error[1] end as lambda_angle_error_max,
cast (discovered as integer)   AS discovered,
app_planet.updated as updated,
app_planet.remarks as remarks,
app_planet.other_web as other_web,
app_planet.angular_distance as angular_distance,
app_planet.temp_calculated as temp_calculated,
app_planet.temp_measured as temp_measured,
app_planet.hot_point_lon as hot_point_lon,
app_planet.log_g as log_g,
app_planet.created as created,
app_planet.modified as modified,

app_planet.albedo as albedo,
albedo_error[1] as albedo_error_min,
case when albedo_error[2] IS NOT NULL then albedo_error[2] else albedo_error[1] end as albedo_error_max,
case  app_planet.mass_detection_type
 when '1' then cast('Radial Velocity' as TEXT) 
 when '2' then cast('Pulsar' as TEXT) 
 when '3' then cast('Controversial' as TEXT) 
 when '4' then cast('Microlensing' as TEXT) 
 when '5' then cast('Imaging' as TEXT) 
 when '6' then cast('Primary Transit' as TEXT) 
 when '7' then cast('Astrometry' as TEXT) 
 when '8' then cast('TTV' as TEXT) 
 when '9' then cast('Other' as TEXT) 
 when '10' then cast('Spectrum' as TEXT) 
 when '11' then cast('Theoretical' as TEXT) 
 when '12' then cast('Flux' as TEXT) 
 when '13' then cast('Secondary Transit' as TEXT) 
 when '14' then cast('IR Excess' as TEXT) 
 ELSE CAST(NULL AS TEXT) END
  as mass_detection_type,

case  app_planet.radius_detection_type
  when '1' then cast('Radial Velocity' as TEXT) 
  when '2' then cast('Pulsar' as TEXT) 
  when '3' then cast('Controversial' as TEXT) 
  when '4' then cast('Microlensing' as TEXT) 
  when '5' then cast('Imaging' as TEXT) 
  when '6' then cast('Primary Transit' as TEXT) 
  when '7' then cast('Astrometry' as TEXT) 
  when '8' then cast('TTV' as TEXT) 
  when '9' then cast('Other' as TEXT) 
  when '10' then cast('Spectrum' as TEXT) 
  when '11' then cast('Theoretical' as TEXT) 
  when '12' then cast('Flux' as TEXT) 
  when '13' then cast('Secondary Transit' as TEXT) 
  when '14' then cast('IR Excess' as TEXT) 
  ELSE CAST(NULL AS TEXT) END
  as radius_detection_type,

app_planet.mass_sini as mass_sin_i,
mass_sini_error[1] as mass_sin_i_error_min,
case when mass_sini_error[2] IS NOT NULL then mass_sini_error[2] else mass_sini_error[1] end as mass_sin_i_error_max,
app_planet.impact_parameter as impact_parameter,
impact_parameter_error[1] as impact_parameter_error_min,
case when impact_parameter_error[2] IS NOT NULL then impact_parameter_error[2] else impact_parameter_error[1] end as impact_parameter_error_max,
app_planet."K" as k,
"K_error"[1] as k_error_min,
case when "K_error"[2] IS NOT NULL then "K_error"[2] else "K_error"[1] end as k_error_max,
array_to_string(ARRAY( SELECT app_alternate_planet_name_1.name FROM app_alternate_planet_name app_alternate_planet_name_1 WHERE app_planet.id = app_alternate_planet_name_1.planet_id),'#') as alternate_name,
app_star.name as star_name,
app_star.distance as star_distance,
app_star.distance_error[1] as star_distance_error_min,
case when app_star.distance_error[2] IS NOT NULL then app_star.distance_error[2] else app_star.distance_error[1] end as star_distance_error_max,
app_star.spec_type as star_spec_type,
app_star.magnitude_v as mag_v,
app_star.magnitude_i as mag_i,
app_star.magnitude_j as mag_j,
app_star.magnitude_h as mag_h,
app_star.magnitude_k as mag_k,
app_star.metallicity as star_metallicity,
app_star.mass as star_mass,
app_star.radius as star_radius,
app_star.age as star_age,
app_star.teff as star_teff,
app_star.magnetic_field as magnetic_field,
case  app_star.detected_disc
  when '5' then cast('Imaging' as TEXT) 
  when '14' then cast('IR Excess' as TEXT) 
  ELSE CAST(NULL AS TEXT) END
  AS detected_disc
FROM app_planet LEFT OUTER JOIN
app_planet2star ON (app_planet.id =
app_planet2star.planet_id) LEFT OUTER JOIN app_star ON
(app_planet2star.star_id = app_star.id) 
WHERE (NOT
detection_type @> ARRAY[3] AND app_planet.status = 1 and app_planet.planet_status = 1) ORDER BY
app_planet.name 
;

GRANT ALL PRIVILEGES ON epn_vue to gavo_user WITH GRANT OPTION;



