From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Karen Springer" <karen(dot)springer(at)wulfsberg(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org, "Paul Malinowski" <paul(dot)malinowski(at)wulfsberg(dot)com> |
Subject: | Re: recursive function |
Date: | 2007-06-13 19:19:45 |
Message-ID: | 162867790706131219k68af4b7ag1eb3f2bf15ba683b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Stefan Kaltenbrunner | 2007-06-13 19:26:07 | Re: pointer to feature comparisons, please |
Previous Message | Andrus | 2007-06-13 18:40:38 | Re: how to speed up query |