Project

General

Profile

1
-- 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 STABLE STRICT
64
  COST 100;
(6-6/6)