Project

General

Profile

« Previous | Next » 

Revision 4538

Added by Aaron Marcuse-Kubitza over 12 years ago

inputs/VegBank/vegbank.~.utils.sql: Removed hardcoded schema name, which is set dynamically by input.Makefile using `SET search_path`

View differences:

inputs/VegBank/vegbank.~.utils.sql
1 
-- Function: "VegBank".plantconcept_ancestors(integer)
1 
-- Function: plantconcept_ancestors(integer)
2 2 

  		




  3
  
  
    
-- DROP FUNCTION "VegBank".plantconcept_ancestors(integer);


  		




  
  3
  
    
-- DROP FUNCTION plantconcept_ancestors(integer);


  		




  4
  4
  
    

  		




  5
  
  
    
CREATE OR REPLACE FUNCTION "VegBank".plantconcept_ancestors(IN plantconcept_id_ integer, OUT plantconcept_id integer, OUT plantlevel character varying)


  		




  
  5
  
    
CREATE OR REPLACE FUNCTION plantconcept_ancestors(IN plantconcept_id_ integer, OUT plantconcept_id integer, OUT plantlevel character varying)


  		




  6
  6
  
    
  RETURNS SETOF record AS


  		




  7
  7
  
    
$BODY$


  		




  8
  8
  
    
DECLARE


  		




  9
  
  
    
    plantparent_id "VegBank".plantstatus.plantparent_id%TYPE;


  		




  
  9
  
    
    plantparent_id plantstatus.plantparent_id%TYPE;


  		




  10
  10
  
    
BEGIN


  		




  11
  11
  
    
    -- Get plantparent_id. Remove plantconcept_id if no match or no plantstatus.


  		




  12
  12
  
    
    SELECT


  		




  ......




  14
  14
  
    
    , plantstatus.plantlevel


  		




  15
  15
  
    
    , plantstatus.plantparent_id


  		




  16
  16
  
    
    INTO plantconcept_id, plantlevel, plantparent_id


  		




  17
  
  
    
    FROM "VegBank".plantstatus


  		




  
  17
  
    
    FROM plantstatus


  		




  18
  18
  
    
    WHERE plantstatus.plantconcept_id = plantconcept_id_


  		




  19
  19
  
    
    ;


  		




  20
  20
  
    
    -- Add this level to the list (output params' current values)


  		




  ......




  22
  22
  
    
    -- Recurse


  		




  23
  23
  
    
    IF plantparent_id IS NOT NULL THEN


  		




  24
  24
  
    
        RETURN QUERY (


  		




  25
  
  
    
            SELECT * FROM "VegBank"."plantconcept_ancestors"(plantparent_id)


  		




  
  25
  
    
            SELECT * FROM "plantconcept_ancestors"(plantparent_id)


  		




  26
  26
  
    
        );


  		




  27
  27
  
    
    END IF;


  		




  28
  28
  
    
END;


  		




  ......




  31
  31
  
    
  COST 100


  		




  32
  32
  
    
  ROWS 10;


  		




  33
  33
  
    

  		




  34
  
  
    
-- Function: "VegBank".plantconcept_plantnames(integer)


  		




  
  34
  
    
-- Function: plantconcept_plantnames(integer)


  		




  35
  35
  
    

  		




  36
  
  
    
-- DROP FUNCTION "VegBank".plantconcept_plantnames(integer);


  		




  
  36
  
    
-- DROP FUNCTION plantconcept_plantnames(integer);


  		




  37
  37
  
    

  		




  38
  
  
    
CREATE OR REPLACE FUNCTION "VegBank".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
  41
  
    
BEGIN


  		




  42
  42
  
    
    -- Get plantconcept ancestors


  		




  43
  43
  
    
    CREATE TEMP TABLE level_plantname AS


  		




  44
  44
  
    
    SELECT plantlevel, plantname.plantname


  		




  45
  
  
    
    FROM "VegBank".plantconcept_ancestors(plantconcept_id_)


  		




  46
  
  
    
    JOIN "VegBank".plantconcept USING (plantconcept_id)


  		




  47
  
  
    
    JOIN "VegBank".plantname USING (plantname_id)


  		




  
  45
  
    
    FROM plantconcept_ancestors(plantconcept_id_)


  		




  
  46
  
    
    JOIN plantconcept USING (plantconcept_id)


  		




  
  47
  
    
    JOIN plantname USING (plantname_id)


  		




  48
  48
  
    
    ;


  		




  49
  49
  
    
    


  		




  50
  50
  
    
    -- Extract name for each level


  		












Also available in:  Unified diff