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