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@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
---------------------------(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
