Project

General

Profile

« Previous | Next » 

Revision 12246

Added by Aaron Marcuse-Kubitza almost 11 years ago

bugfix: inputs/bien2_traits/validations.sql: use a wrapper function for util.ifnull() so that the views don't get dropped when the util schema is reinstalled

View differences:

trunk/inputs/bien2_traits/validations.-.util.sql
1 
CREATE OR REPLACE FUNCTION ifnull(value anyelement, null_ anyelement)
2 
  RETURNS anyelement AS
3 
$BODY$
4 
SELECT util.ifnull($1, $2)
5 
$BODY$
6 
  LANGUAGE sql IMMUTABLE
7 
  COST 100;
8 
COMMENT ON FUNCTION ifnull(anyelement, anyelement) IS '
9 
wrapper that prevents views from getting dropped when the util schema is reinstalled
10 
';
trunk/inputs/bien2_traits/validations.sql
60 60 
--
61 61 

  		




  62
  62
  
    
CREATE VIEW _traits_05_count_taxa AS


  		




  63
  
  
    
 SELECT count(DISTINCT btrim(concat_ws(' '::text, util.ifnull("TraitObservation"."taxonName", ''::text), util.ifnull("TraitObservation"."scientificNameAuthorship", ''::text)))) AS taxa


  		




  
  63
  
    
 SELECT count(DISTINCT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text)))) AS taxa


  		




  64
  64
  
    
   FROM "TraitObservation"


  		




  65
  65
  
    
  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));


  		




  66
  66
  
    

  		




  ......




  70
  70
  
    
--


  		




  71
  71
  
    

  		




  72
  72
  
    
CREATE VIEW _traits_06_list_distinct_taxa AS


  		




  73
  
  
    
 SELECT DISTINCT btrim(concat_ws(' '::text, util.ifnull("TraitObservation"."taxonName", ''::text), util.ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor


  		




  
  73
  
    
 SELECT DISTINCT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor


  		




  74
  74
  
    
   FROM "TraitObservation"


  		




  75
  75
  
    
  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))


  		




  76
  
  
    
  ORDER BY btrim(concat_ws(' '::text, util.ifnull("TraitObservation"."taxonName", ''::text), util.ifnull("TraitObservation"."scientificNameAuthorship", ''::text)));


  		




  
  76
  
    
  ORDER BY btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text)));


  		




  77
  77
  
    

  		




  78
  78
  
    

  		




  79
  79
  
    
--


  		




  ......




  94
  94
  
    
--


  		




  95
  95
  
    

  		




  96
  96
  
    
CREATE VIEW _traits_08_taxonname_trait_and_value_for_first_5000_records AS


  		




  97
  
  
    
 SELECT btrim(concat_ws(' '::text, util.ifnull("TraitObservation"."taxonName", ''::text), util.ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor, 


  		




  
  97
  
    
 SELECT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor, 


  		




  98
  98
  
    
    "TraitObservation"."measurementName" AS trait, 


  		




  99
  99
  
    
    "TraitObservation"."measurementValue" AS value


  		




  100
  100
  
    
   FROM "TraitObservation"


  		




  101
  101
  
    
  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))


  		




  102
  
  
    
  ORDER BY btrim(concat_ws(' '::text, util.ifnull("TraitObservation"."taxonName", ''::text), util.ifnull("TraitObservation"."scientificNameAuthorship", ''::text))), "TraitObservation"."measurementName", "TraitObservation"."measurementValue";


  		




  
  102
  
    
  ORDER BY btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text))), "TraitObservation"."measurementName", "TraitObservation"."measurementValue";


  		




  103
  103
  
    

  		




  104
  104
  
    

  		




  105
  105
  
    
--


  		












Also available in:  Unified diff