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