-- Function: "VegBank".plantconcept_plantnames(integer)

-- DROP FUNCTION "VegBank".plantconcept_plantnames(integer);

CREATE OR REPLACE FUNCTION "VegBank".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$

BEGIN

    -- Get plantconcept ancestors

    CREATE TEMP TABLE level_plantname AS

    SELECT plantlevel, plantname.plantname

    FROM "VegBank".plantconcept_ancestors(plantconcept_id_)

    JOIN "VegBank".plantconcept USING (plantconcept_id)

    JOIN "VegBank".plantname USING (plantname_id)

    ;

    -- Extract name for each level

    "Kingdom"       := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Kingdom');

    "Subkingdom"    := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subkingdom');

    "Superdivision" := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Superdivision');

    "Division"      := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Division');

    "Subdivision"   := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subdivision');

    "Class"         := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Class');

    "Subclass"      := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subclass');

    "Order"         := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Order');

    "Family"        := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Family');

    "Genus"         := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Genus');

    "Species"       := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Species');

    "Subspecies"    := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Subspecies');

    "Variety"       := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Variety');

    "Forma"         := (SELECT plantname FROM level_plantname WHERE plantlevel = 'Forma');

    -- Clean up

    DROP TABLE level_plantname;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE STRICT

  COST 100;