Project

General

Profile

« Previous | Next » 

Revision 6815

Added by Aaron Marcuse-Kubitza almost 12 years ago

inputs/CVS/cvs.~.utils.sql: plantconcept_*(): Use plantConcept.lowestParentConcept_ID,taxonLevel instead of plantStatus.plantParent_ID,plantLevel to find the plantConcept's ancestors, because CVS does not use plantStatus except in very few cases and instead puts the parent link directly in plantConcept

View differences:

inputs/CVS/cvs.~.utils.sql
2 2 

  		




  3
  3
  
    
-- DROP FUNCTION plantconcept_ancestors(integer);


  		




  4
  4
  
    

  		




  5
  
  
    
CREATE OR REPLACE FUNCTION 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 "taxonLevel" character varying)


  		




  6
  6
  
    
  RETURNS SETOF record AS


  		




  7
  7
  
    
$BODY$


  		




  8
  8
  
    
DECLARE


  		




  9
  
  
    
    "plantParent_ID" "plantStatus"."plantParent_ID"%TYPE;


  		




  
  9
  
    
    "lowestParentConcept_ID" "plantConcept"."lowestParentConcept_ID"%TYPE;


  		




  10
  10
  
    
BEGIN


  		




  11
  
  
    
    -- Get "plantParent_ID". Remove "PLANTCONCEPT_ID" if no match or no "plantStatus".


  		




  
  11
  
    
    -- Get "lowestParentConcept_ID". Remove "PLANTCONCEPT_ID" if no match or no "plantConcept".


  		




  12
  12
  
    
    SELECT


  		




  13
  
  
    
    "plantStatus"."PLANTCONCEPT_ID"


  		




  14
  
  
    
    , "plantStatus"."plantLevel"


  		




  15
  
  
    
    , "plantStatus"."plantParent_ID"


  		




  16
  
  
    
    INTO "PLANTCONCEPT_ID", "plantLevel", "plantParent_ID"


  		




  17
  
  
    
    FROM "plantStatus"


  		




  18
  
  
    
    WHERE "plantStatus"."PLANTCONCEPT_ID" = "PLANTCONCEPT_ID_"


  		




  
  13
  
    
    "plantConcept"."PLANTCONCEPT_ID"


  		




  
  14
  
    
    , "plantConcept"."taxonLevel"


  		




  
  15
  
    
    , "plantConcept"."lowestParentConcept_ID"


  		




  
  16
  
    
    INTO "PLANTCONCEPT_ID", "taxonLevel", "lowestParentConcept_ID"


  		




  
  17
  
    
    FROM "plantConcept"


  		




  
  18
  
    
    WHERE "plantConcept"."PLANTCONCEPT_ID" = "PLANTCONCEPT_ID_"


  		




  19
  19
  
    
    ;


  		




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


  		




  21
  21
  
    
    RETURN NEXT;


  		




  22
  22
  
    
    -- Recurse


  		




  23
  
  
    
    IF "plantParent_ID" IS NOT NULL THEN


  		




  
  23
  
    
    IF "lowestParentConcept_ID" IS NOT NULL THEN


  		




  24
  24
  
    
        RETURN QUERY (


  		




  25
  
  
    
            SELECT * FROM "plantconcept_ancestors"("plantParent_ID")


  		




  
  25
  
    
            SELECT * FROM "plantconcept_ancestors"("lowestParentConcept_ID")


  		




  26
  26
  
    
        );


  		




  27
  27
  
    
    END IF;


  		




  28
  28
  
    
END;


  		




  ......




  39
  39
  
    
  RETURNS record AS


  		




  40
  40
  
    
$BODY$


  		




  41
  41
  
    
WITH "level_plantName" AS (


  		




  42
  
  
    
    SELECT "plantLevel", "plantName"."plantName"


  		




  
  42
  
    
    SELECT plantconcept_ancestors."taxonLevel", "plantName"."plantName"


  		




  43
  43
  
    
    FROM plantconcept_ancestors($1)


  		




  44
  44
  
    
    JOIN "plantConcept" USING ("PLANTCONCEPT_ID")


  		




  45
  45
  
    
    JOIN "plantName" USING ("PLANTNAME_ID")


  		




  46
  46
  
    
)


  		




  47
  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')


  		




  
  48
  
    
  (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Kingdom')


  		




  
  49
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Subkingdom')


  		




  
  50
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Superdivision')


  		




  
  51
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Division')


  		




  
  52
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Subdivision')


  		




  
  53
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Class')


  		




  
  54
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Subclass')


  		




  
  55
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Order')


  		




  
  56
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Family')


  		




  
  57
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Genus')


  		




  
  58
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Species')


  		




  
  59
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Subspecies')


  		




  
  60
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Variety')


  		




  
  61
  
    
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Forma')


  		




  62
  62
  
    
$BODY$


  		




  63
  63
  
    
  LANGUAGE sql STABLE STRICT


  		




  64
  64
  
    
  COST 100;


  		












Also available in:  Unified diff