| 1 |
4519
|
aaronmk
|
-- Function: "VegBank".plantconcept_ancestors(integer)
|
| 2 |
|
|
|
| 3 |
|
|
-- DROP FUNCTION "VegBank".plantconcept_ancestors(integer);
|
| 4 |
|
|
|
| 5 |
4520
|
aaronmk
|
CREATE OR REPLACE FUNCTION "VegBank".plantconcept_ancestors(IN plantconcept_id_ integer, OUT plantconcept_id integer, OUT plantlevel character varying)
|
| 6 |
4519
|
aaronmk
|
RETURNS SETOF record AS
|
| 7 |
|
|
$BODY$
|
| 8 |
|
|
DECLARE
|
| 9 |
|
|
plantparent_id "VegBank".plantstatus.plantparent_id%TYPE;
|
| 10 |
|
|
BEGIN
|
| 11 |
|
|
-- Get plantparent_id. Remove plantconcept_id if no match or no plantstatus.
|
| 12 |
4520
|
aaronmk
|
SELECT
|
| 13 |
|
|
plantstatus.plantconcept_id
|
| 14 |
|
|
, plantstatus.plantlevel
|
| 15 |
|
|
, plantstatus.plantparent_id
|
| 16 |
|
|
INTO plantconcept_id, plantlevel, plantparent_id
|
| 17 |
4519
|
aaronmk
|
FROM "VegBank".plantstatus
|
| 18 |
4520
|
aaronmk
|
WHERE plantstatus.plantconcept_id = plantconcept_id_
|
| 19 |
4519
|
aaronmk
|
;
|
| 20 |
|
|
-- Add this level to the list (output params' current values)
|
| 21 |
|
|
RETURN NEXT;
|
| 22 |
|
|
-- Recurse
|
| 23 |
|
|
IF plantparent_id IS NOT NULL THEN
|
| 24 |
|
|
RETURN QUERY (
|
| 25 |
|
|
SELECT * FROM "VegBank"."plantconcept_ancestors"(plantparent_id)
|
| 26 |
|
|
);
|
| 27 |
|
|
END IF;
|
| 28 |
|
|
END;
|
| 29 |
|
|
$BODY$
|
| 30 |
|
|
LANGUAGE plpgsql IMMUTABLE STRICT
|
| 31 |
|
|
COST 100
|
| 32 |
|
|
ROWS 10;
|
