Thread: recursive function

recursive function

From

Karen Springer

Date:

13 June 2007, 18:42:21

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

Re: recursive function

From

"Pavel Stehule"

Date:

13 June 2007, 19:19:54

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
>

Re: recursive function

From

Karen Springer

Date:

09 July 2007, 15:20:21

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

plpgsql equivalent to plperl $_SHARED and plpythonu global dictionary GD?

From

"Zlatko Matic"

Date:

09 July 2007, 15:26:42

Does plpgsql has something equivalent to plperl $_SHARED or plpythonu global
dictionary GD?
Thanks,

Zlatko

Re: plpgsql equivalent to plperl $_SHARED and plpythonu global dictionary GD?

From

"hubert depesz lubaczewski"

Date:

10 July 2007, 08:17:21

On 7/9/07, Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:

Does plpgsql has something equivalent to plperl $_SHARED or plpythonu global
dictionary GD?

no, but you can use some table to emulate this. or a temp table.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

Re: plpgsql equivalent to plperl $_SHARED and plpythonu global dictionary GD?

From

"Zlatko Matic"

Date:

10 July 2007, 12:29:53

Hello.

OK. I created a new table that holds information about rows inserted/updated in a transaction.

I realized that after row-level trigger fires always before after statement-level trigger.

Therefore I can use row-level triger to populate the auxiliary table which holds information about affected rows, so that after statement-level trigger can read that information.

It works and is fast enough.

So, I emulated NEW and OLD for statement level trigger:)

Regards,

Zlatko

----- Original Message -----
From: hubert depesz lubaczewski
To: Zlatko Matic
Cc: pgsql-general@postgresql.org
Sent: Tuesday, July 10, 2007 10:17 AM
Subject: Re: [GENERAL] plpgsql equivalent to plperl $_SHARED and plpythonu global dictionary GD?

On 7/9/07, Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:
Does plpgsql has something equivalent to plperl $_SHARED or plpythonu global
dictionary GD?

no, but you can use some table to emulate this. or a temp table.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz