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