-- Function: plantconcept_ancestors(integer)

-- DROP FUNCTION plantconcept_ancestors(integer);

CREATE OR REPLACE FUNCTION plantconcept_ancestors(IN "PLANTCONCEPT_ID_" integer, OUT "PLANTCONCEPT_ID" integer, OUT "plantLevel" character varying)
  RETURNS SETOF record AS
$BODY$
DECLARE
    "plantParent_ID" "plantStatus"."plantParent_ID"%TYPE;
BEGIN
    -- Get "plantParent_ID". Remove "PLANTCONCEPT_ID" if no match or no "plantStatus".
    SELECT
    "plantStatus"."PLANTCONCEPT_ID"
    , "plantStatus"."plantLevel"
    , "plantStatus"."plantParent_ID"
    INTO "PLANTCONCEPT_ID", "plantLevel", "plantParent_ID"
    FROM "plantStatus"
    WHERE "plantStatus"."PLANTCONCEPT_ID" = "PLANTCONCEPT_ID_"
    ;
    -- Add this level to the list (output params' current values)
    RETURN NEXT;
    -- Recurse
    IF "plantParent_ID" IS NOT NULL THEN
        RETURN QUERY (
            SELECT * FROM "plantconcept_ancestors"("plantParent_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 "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)
  RETURNS record AS
$BODY$
WITH "level_plantName" AS (
    SELECT "plantLevel", "plantName"."plantName"
    FROM plantconcept_ancestors($1)
    JOIN "plantConcept" USING ("PLANTCONCEPT_ID")
    JOIN "plantName" USING ("PLANTNAME_ID")
)
SELECT
  (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Kingdom')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Subkingdom')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Superdivision')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Division')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Subdivision')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Class')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Subclass')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Order')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Family')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Genus')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Species')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Subspecies')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Variety')
, (SELECT "plantName" FROM "level_plantName" WHERE "plantLevel" = 'Forma')
$BODY$
  LANGUAGE sql VOLATILE STRICT
  COST 100;