Re: recursive function

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(dot)springer(at)wulfsberg(dot)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
>