|
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 "taxonLevel" character varying)
|
|
6
|
RETURNS SETOF record AS
|
|
7
|
$BODY$
|
|
8
|
DECLARE
|
|
9
|
"lowestParentConcept_ID" "plantConcept"."lowestParentConcept_ID"%TYPE;
|
|
10
|
BEGIN
|
|
11
|
-- Get "lowestParentConcept_ID". Remove "PLANTCONCEPT_ID" if no match or no "plantConcept".
|
|
12
|
SELECT
|
|
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
|
;
|
|
20
|
-- Add this level to the list (output params' current values)
|
|
21
|
RETURN NEXT;
|
|
22
|
-- Recurse
|
|
23
|
IF "lowestParentConcept_ID" IS NOT NULL THEN
|
|
24
|
RETURN QUERY (
|
|
25
|
SELECT * FROM "plantconcept_ancestors"("lowestParentConcept_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 "Division" text, OUT "Class" text, OUT "Subclass" text, OUT "Order" text, OUT "composite family" text, OUT "family" text, OUT "tribe" text, OUT "composite genus" text, OUT "genus" text, OUT "subgenus" text, OUT "section" text, OUT "composite species" text, OUT "species" text, OUT "subspecies" text, OUT "composite variety" text, OUT "variety" text)
|
|
39
|
RETURNS record AS
|
|
40
|
$BODY$
|
|
41
|
WITH "level_plantName" AS (
|
|
42
|
SELECT plantconcept_ancestors."taxonLevel", "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 "taxonLevel" = 'Kingdom')
|
|
49
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Subkingdom')
|
|
50
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Division')
|
|
51
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Class')
|
|
52
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Subclass')
|
|
53
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Order')
|
|
54
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'composite family')
|
|
55
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'family')
|
|
56
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'tribe')
|
|
57
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'composite genus')
|
|
58
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'genus')
|
|
59
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'subgenus')
|
|
60
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'section')
|
|
61
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'composite species')
|
|
62
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'species')
|
|
63
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'subspecies')
|
|
64
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'composite variety')
|
|
65
|
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'variety')
|
|
66
|
$BODY$
|
|
67
|
LANGUAGE sql STABLE STRICT
|
|
68
|
COST 100;
|
