Project

General

Profile

1 6742 aaronmk
-- Function: plantconcept_ancestors(integer)
2
3
-- DROP FUNCTION plantconcept_ancestors(integer);
4
5
CREATE OR REPLACE FUNCTION plantconcept_ancestors(IN "PLANTCONCEPT_ID_" integer, OUT "PLANTCONCEPT_ID" integer, OUT "plantLevel" character varying)
6
  RETURNS SETOF record AS
7
$BODY$
8
DECLARE
9
    "plantParent_ID" "plantStatus"."plantParent_ID"%TYPE;
10
BEGIN
11
    -- Get "plantParent_ID". Remove "PLANTCONCEPT_ID" if no match or no "plantStatus".
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_"
19
    ;
20
    -- Add this level to the list (output params' current values)
21
    RETURN NEXT;
22
    -- Recurse
23
    IF "plantParent_ID" IS NOT NULL THEN
24
        RETURN QUERY (
25
            SELECT * FROM "plantconcept_ancestors"("plantParent_ID")
26
        );
27
    END IF;
28
END;
29
$BODY$
30
  LANGUAGE plpgsql STABLE STRICT
31
  COST 100
32
  ROWS 10;
33
34
-- Function: plantconcept_plantnames(integer)
35
36
-- DROP FUNCTION plantconcept_plantnames(integer);
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)
39
  RETURNS record AS
40
$BODY$
41
WITH "level_plantName" AS (
42
    SELECT "plantLevel", "plantName"."plantName"
43
    FROM plantconcept_ancestors($1)
44
    JOIN "plantConcept" USING ("PLANTCONCEPT_ID")
45
    JOIN "plantName" USING ("PLANTNAME_ID")
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')
62
$BODY$
63
  LANGUAGE sql VOLATILE STRICT
64
  COST 100;