Re: recursive function - Mailing list pgsql-general

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
>