Project

General

Profile

« Previous | Next » 

Revision 4539

Added by Aaron Marcuse-Kubitza over 12 years ago

inputs/VegBank/vegbank.~.utils.sql: plantconcept_plantnames(): Use SQL SELECT query and WITH clause (http://www.postgresql.org/docs/8.4/static/queries-with.html) instead of temp table, because PostgreSQL does not support using temp tables inside functions that are called repeatedly (http://archives.postgresql.org/pgsql-general/2006-02/msg00516.php; it results in an "out of shared memory" error)

View differences:

inputs/VegBank/vegbank.~.utils.sql
35 35 

  		




  36
  36
  
    
-- DROP FUNCTION plantconcept_plantnames(integer);


  		




  37
  37
  
    

  		




  38
  
  
    
CREATE OR REPLACE FUNCTION plantconcept_plantnames(IN plantconcept_id_ integer, OUT "Kingdom" text, OUT "Subkingdom" text, OUT "Superdivision" text, OUT "Division" text, OUT "Subdivision" text, OUT "Class" text, OUT "Subclass" text, OUT "Order" text, OUT "Family" text, OUT "Genus" text, OUT "Species" text, OUT "Subspecies" text, OUT "Variety" text, OUT "Forma" text)


  		




  
  38
  
    
CREATE OR REPLACE FUNCTION plantconcept_plantnames(IN plantconcept_id integer, OUT "Kingdom" text, OUT "Subkingdom" text, OUT "Superdivision" text, OUT "Division" text, OUT "Subdivision" text, OUT "Class" text, OUT "Subclass" text, OUT "Order" text, OUT "Family" text, OUT "Genus" text, OUT "Species" text, OUT "Subspecies" text, OUT "Variety" text, OUT "Forma" text)


  		




  39
  39
  
    
  RETURNS record AS


  		




  40
  40
  
    
$BODY$


  		




  41
  
  
    
BEGIN


  		




  42
  
  
    
    -- Get plantconcept ancestors


  		




  43
  
  
    
    CREATE TEMP TABLE level_plantname AS


  		




  
  41
  
    
WITH level_plantname AS (


  		




  44
  42
  
    
    SELECT plantlevel, plantname.plantname


  		




  45
  
  
    
    FROM plantconcept_ancestors(plantconcept_id_)


  		




  
  43
  
    
    FROM plantconcept_ancestors($1)


  		




  46
  44
  
    
    JOIN plantconcept USING (plantconcept_id)


  		




  47
  45
  
    
    JOIN plantname USING (plantname_id)


  		




  48
  
  
    
    ;


  		




  49
  
  
    
    


  		




  50
  
  
    
    -- Extract name for each level


  		




  51
  
  
    
    "Kingdom"       := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Kingdom');


  		




  52
  
  
    
    "Subkingdom"    := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subkingdom');


  		




  53
  
  
    
    "Superdivision" := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Superdivision');


  		




  54
  
  
    
    "Division"      := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Division');


  		




  55
  
  
    
    "Subdivision"   := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subdivision');


  		




  56
  
  
    
    "Class"         := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Class');


  		




  57
  
  
    
    "Subclass"      := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subclass');


  		




  58
  
  
    
    "Order"         := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Order');


  		




  59
  
  
    
    "Family"        := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Family');


  		




  60
  
  
    
    "Genus"         := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Genus');


  		




  61
  
  
    
    "Species"       := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Species');


  		




  62
  
  
    
    "Subspecies"    := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subspecies');


  		




  63
  
  
    
    "Variety"       := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Variety');


  		




  64
  
  
    
    "Forma"         := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Forma');


  		




  65
  
  
    
    


  		




  66
  
  
    
    -- Clean up


  		




  67
  
  
    
    DROP TABLE level_plantname;


  		




  68
  
  
    
END;


  		




  
  46
  
    
)


  		




  
  47
  
    
SELECT


  		




  
  48
  
    
  (SELECT plantname FROM level_plantname WHERE plantlevel = 'Kingdom')


  		




  
  49
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subkingdom')


  		




  
  50
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Superdivision')


  		




  
  51
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Division')


  		




  
  52
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subdivision')


  		




  
  53
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Class')


  		




  
  54
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subclass')


  		




  
  55
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Order')


  		




  
  56
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Family')


  		




  
  57
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Genus')


  		




  
  58
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Species')


  		




  
  59
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subspecies')


  		




  
  60
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Variety')


  		




  
  61
  
    
, (SELECT plantname FROM level_plantname WHERE plantlevel = 'Forma')


  		




  69
  62
  
    
$BODY$


  		




  70
  
  
    
  LANGUAGE plpgsql VOLATILE STRICT


  		




  
  63
  
    
  LANGUAGE sql VOLATILE STRICT


  		




  71
  64
  
    
  COST 100;


  		












Also available in:  Unified diff