recursive function

[Karen Springer <karen.springer@xxxxxxxxxxxxx>]

Hi,

I am struggling to write my first recursive function and think I'm 
missing something basic.  I have written 2 functions that work if I pass 
over one parameter, but if I try to pass over a series of parameters say 
in a view for every field in the table it seems to run but never 
displays data.

I have a table of built units.

tbl_BuiltAssemblies
ParentBarCode varchar(12)
ChildBarCode varchar(12)

I need to find the end ParentBarCode (Top Level) for each child.  So if 
I have

Parent   Child
1            2
2            3
2            4
3            5

If I feed the function child 5, 4, 3, or 2, I need to get parent 1.  
Since these are built units, each child can only be in one parent.

So far I've written this function which works great when passing over 
one parameter.

CREATE OR REPLACE FUNCTION 
"Production_Tracking"."GetTopLevelParent_WithView"(varchar)
RETURNS TEXT AS '

DECLARE
   childBarCode ALIAS FOR $1;
   parentBarCode TEXT;
   topLevelParentBarCode TEXT;
BEGIN

   SELECT INTO parentBarCode
          "tbl_BuiltAssemblies"."ParentBarCode"
   FROM "Production_Tracking"."tbl_BuiltAssemblies"
   WHERE "tbl_BuiltAssemblies"."ChildBarCode" = childBarCode;

   topLevelParentBarCode = parentBarCode;

   WHILE FOUND LOOP
       SELECT INTO parentBarCode
              "tbl_BuiltAssemblies"."ParentBarCode"
       FROM "Production_Tracking"."tbl_BuiltAssemblies"
       WHERE "tbl_BuiltAssemblies"."ChildBarCode" = parentBarCode;
       IF NOT(parentBarCode IS NULL) THEN
           topLevelParentBarCode = parentBarCode;
       END IF;
   END LOOP;

   RETURN (topLevelParentBarCode)::TEXT;

END;
' LANGUAGE 'plpgsql';

I have also written this too which again works great if I pass over one 
parameter.  (I would add a Level field to this & get the max level 
eventually, but I don't want to spend more time on it until I know I'm 
on the right track.)

CREATE OR REPLACE FUNCTION 
"Production_Tracking"."GetTopLevelParent_WithView_1"(varchar)
RETURNS SETOF "Production_Tracking".cattree AS'

DECLARE
   childbarcode ALIAS FOR $1;
   parentbarcode "Production_Tracking".cattree%ROWTYPE;
   toplevelparentbarcode "Production_Tracking".cattree%ROWTYPE;
BEGIN

   FOR parentbarcode IN SELECT "ParentBarCode", childbarcode AS 
"InitialChild"
       FROM "Production_Tracking"."tbl_BuiltAssemblies" WHERE 
"tbl_BuiltAssemblies"."ChildBarCode" = childbarcode LOOP

       RETURN NEXT parentbarcode;

       FOR toplevelparentbarcode IN SELECT "ParentBarCode", 
childbarcode AS "InitialChild"
           FROM 
"Production_Tracking"."GetTopLevelParent_WithView_1"(parentbarcode."ParentBarCode") 
LOOP

           RETURN NEXT toplevelparentbarcode;

       END LOOP;
      
   END LOOP;

   RETURN;

END;
' LANGUAGE 'plpgsql';

Here are examples of the views I've tried

SELECT "tbl_BuiltAssemblies"."ChildBarCode",
  
"Production_Tracking"."GetTopLevelParent_WithView"("tbl_BuiltAssemblies"."ChildBarCode") 
AS "TopLevelParent"
  FROM "Production_Tracking"."tbl_BuiltAssemblies";

and

SELECT "tbl_BuiltAssemblies"."ChildBarCode",
  
"Production_Tracking"."GetTopLevelParent_WithView_1"("tbl_BuiltAssemblies"."ChildBarCode") 
AS parents
  FROM "Production_Tracking"."tbl_BuiltAssemblies";

These views seem to run, but never display data.

Any help would be most appreciated.

Thanks,
Karen