-- 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 STABLE STRICT
  COST 100;