| 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;
|
