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

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

CREATE OR REPLACE FUNCTION "VegBank".plantconcept_ancestors(IN plantconcept_id_ integer, OUT ancestor_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 plantconcept_id, plantstatus.plantlevel, plantstatus.plantparent_id

    INTO ancestor_id, plantlevel, plantparent_id

    FROM "VegBank".plantstatus

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