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

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

CREATE OR REPLACE FUNCTION "VegBank".plantconcept_ancestors(IN plantconcept_id_ integer, OUT plantconcept_id integer, OUT plantlevel character varying)
  RETURNS SETOF record AS
$BODY$
DECLARE
    plantparent_id "VegBank".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 "VegBank".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 "VegBank"."plantconcept_ancestors"(plantparent_id)
        );
    END IF;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100
  ROWS 10;