Project

General

Profile

1 4538 aaronmk
-- Function: plantconcept_ancestors(integer)
2 4519 aaronmk
3 4538 aaronmk
-- DROP FUNCTION plantconcept_ancestors(integer);
4 4519 aaronmk
5 4538 aaronmk
CREATE OR REPLACE FUNCTION plantconcept_ancestors(IN plantconcept_id_ integer, OUT plantconcept_id integer, OUT plantlevel character varying)
6 4519 aaronmk
  RETURNS SETOF record AS
7
$BODY$
8
DECLARE
9 4538 aaronmk
    plantparent_id plantstatus.plantparent_id%TYPE;
10 4519 aaronmk
BEGIN
11
    -- Get plantparent_id. Remove plantconcept_id if no match or no plantstatus.
12 4520 aaronmk
    SELECT
13
    plantstatus.plantconcept_id
14
    , plantstatus.plantlevel
15
    , plantstatus.plantparent_id
16
    INTO plantconcept_id, plantlevel, plantparent_id
17 4538 aaronmk
    FROM plantstatus
18 4520 aaronmk
    WHERE plantstatus.plantconcept_id = plantconcept_id_
19 4519 aaronmk
    ;
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 4538 aaronmk
            SELECT * FROM "plantconcept_ancestors"(plantparent_id)
26 4519 aaronmk
        );
27
    END IF;
28
END;
29
$BODY$
30 4536 aaronmk
  LANGUAGE plpgsql STABLE STRICT
31 4519 aaronmk
  COST 100
32
  ROWS 10;
33 4537 aaronmk
34 4538 aaronmk
-- Function: plantconcept_plantnames(integer)
35 4537 aaronmk
36 4538 aaronmk
-- DROP FUNCTION plantconcept_plantnames(integer);
37 4537 aaronmk
38 4539 aaronmk
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 4537 aaronmk
  RETURNS record AS
40
$BODY$
41 4539 aaronmk
WITH level_plantname AS (
42 4537 aaronmk
    SELECT plantlevel, plantname.plantname
43 4539 aaronmk
    FROM plantconcept_ancestors($1)
44 4538 aaronmk
    JOIN plantconcept USING (plantconcept_id)
45
    JOIN plantname USING (plantname_id)
46 4539 aaronmk
)
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 4537 aaronmk
$BODY$
63 4539 aaronmk
  LANGUAGE sql VOLATILE STRICT
64 4537 aaronmk
  COST 100;