| 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 |
6814
|
aaronmk
|
LANGUAGE sql STABLE STRICT
|
| 64 |
4537
|
aaronmk
|
COST 100;
|
