-- Function: plantconcept_ancestors(integer)

-- DROP FUNCTION plantconcept_ancestors(integer);

CREATE OR REPLACE FUNCTION plantconcept_ancestors(IN "PLANTCONCEPT_ID_" integer, OUT "PLANTCONCEPT_ID" integer, OUT "taxonLevel" character varying)
  RETURNS SETOF record AS
$BODY$
DECLARE
    "lowestParentConcept_ID" "plantConcept"."lowestParentConcept_ID"%TYPE;
BEGIN
    -- Get "lowestParentConcept_ID". Remove "PLANTCONCEPT_ID" if no match or no "plantConcept".
    SELECT
    "plantConcept"."PLANTCONCEPT_ID"
    , "plantConcept"."taxonLevel"
    , "plantConcept"."lowestParentConcept_ID"
    INTO "PLANTCONCEPT_ID", "taxonLevel", "lowestParentConcept_ID"
    FROM "plantConcept"
    WHERE "plantConcept"."PLANTCONCEPT_ID" = "PLANTCONCEPT_ID_"
    ;
    -- Add this level to the list (output params' current values)
    RETURN NEXT;
    -- Recurse
    IF "lowestParentConcept_ID" IS NOT NULL THEN
        RETURN QUERY (
            SELECT * FROM "plantconcept_ancestors"("lowestParentConcept_ID")
        );
    END IF;
END;
$BODY$
  LANGUAGE plpgsql STABLE STRICT
  COST 100
  ROWS 10;

-- Function: plantconcept_plantnames(integer)

-- DROP FUNCTION plantconcept_plantnames(integer);

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)
  RETURNS record AS
$BODY$
WITH "level_plantName" AS (
    SELECT plantconcept_ancestors."taxonLevel", "plantName"."plantName"
    FROM plantconcept_ancestors($1)
    JOIN "plantConcept" USING ("PLANTCONCEPT_ID")
    JOIN "plantName" USING ("PLANTNAME_ID")
)
SELECT
  (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Kingdom')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Subkingdom')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Division')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Class')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Subclass')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'Order')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'composite family')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'family')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'tribe')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'composite genus')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'genus')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'subgenus')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'section')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'composite species')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'species')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'subspecies')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'composite variety')
, (SELECT "plantName" FROM "level_plantName" WHERE "taxonLevel" = 'variety')
$BODY$
  LANGUAGE sql STABLE STRICT
  COST 100;