Re: recursive function

Hi,

Thanks Pavel.  Here's my working function.  Maybe it will save someone
else some time.

CREATE OR REPLACE FUNCTION "Production_Tracking"."GetTopLevelParent"()
 RETURNS SETOF record AS
$BODY$

DECLARE

initial_rec RECORD;
rec RECORD;
parentbc varchar;
toplevelparentbc varchar;


BEGIN

FOR initial_rec IN SELECT "ChildBarCode" FROM
"Production_Tracking"."tbl_BuiltAssemblies" LOOP

   SELECT INTO parentbc "tbl_BuiltAssemblies"."ParentBarCode" FROM
"Production_Tracking"."tbl_BuiltAssemblies" WHERE
"tbl_BuiltAssemblies"."ChildBarCode" = initial_rec."ChildBarCode";

   toplevelparentbc := parentbc;

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

   rec := (toplevelparentbc,initial_rec."ChildBarCode");

   RETURN NEXT rec;

END LOOP;


RETURN;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE VIEW "Production_Tracking"."tvw_GetTopLevelParent" AS
SELECT "GetTopLevelParent"."TopLevelParent",
"GetTopLevelParent"."InitialBarCode"
  FROM "Production_Tracking"."GetTopLevelParent"()
"GetTopLevelParent"("TopLevelParent" character varying, "InitialBarCode"
character varying);

Example if given:
ParentBarCode    ChildBarCode
W1                       W2
W2                       W3
W3                       W4
This function will return
toplevelparentbc   ChildBarCode
W1                        W2
W1                        W3
W1                        W4';

Karen

Pavel Stehule wrote:
> Hello
>
> please, look on
> http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html
>
>
> Regards
> Pavel Stehule
>
> 2007/6/13, Karen Springer <karen.springer@wulfsberg.com>:
>> 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
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>>                http://www.postgresql.org/docs/faq
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq