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(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
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq