Re: recursive processing - Mailing list pgsql-novice

> On Wed, 16 Feb 2005 22:23:15 -0500, Keith Worthington wrote
> > Hi All,
> >
> > I have two tables.  The first table (tbl_item) contains an item id
> > and it's type.
> >
> > tbl_item
> > item_id | item_type
> > --------+----------
> >       A | DIR
> >       B | DIR
> >       C | ASY
> >       D | DIR
> >       E | DIR
> >       F | DIR
> >       G | ASY
> >
> > The second table (tbl_assembly) contains the components of the
> > assemblies.  It is possible that an assembly is made up of any
> > quantity of DIR items or one or more assemblies
> >
> > tbl_assembly
> > item_id | component_id | quantity
> > --------+--------------+---------
> >       C |            A | 2
> >       C |            B | 4
> >       G |            C | 3
> >       G |            E | 1
> >       G |            F | 8
> >
> > I would like to perform some recursive processing to replace any
> > assembly used as a component with the appropriate number of
> > components so that all component_ids are of item_type = 'DIR'.
> >
> > item_id | component_id | quantity
> > --------+--------------+---------
> >       C |            A |  2
> >       C |            B |  4
> >       G |            A |  6
> >       G |            B | 12
> >       G |            E |  1
> >       G |            F |  8
> >
> > I want to perform this processing any time an item_id is INSERTed or
> > UPDATEDed into tbl_assembly (TRIGGER) and place this result back
> > into the assembly table.
> >
> > Any assistance and URL's to documentation or examples is appreciated.
> >
> > --
> > Kind Regards,
> > Keith
> >
> > ---------------------------(end of broadcast)---------------------------
> -------- Original Message --------
> Subject:     Re: [NOVICE] recursive processing
> Date:     Wed, 16 Feb 2005 22:02:36 -0800
> From:     Doug Gorley <douggorley@shaw.ca>
> To:     pgsql-novice@postgresql.org
> CC:     KeithW@narrowpathinc.com
>
>
>
> Hi Keith,
>
> This function accomplishes what you want (at least in my testing),
> but I'm no expert on PL/pgSQL programming; If anyone has any
> recommendations on how to do this better, I'd live to hear it.
>
> Thanks,
> Doug Gorley | douggorley (at) shaw (dot) ca
>
> ---
> ---
> create or replace function build_assemblies()
> returns integer
> as $$
> declare
>   compound tbl_assembly%ROWTYPE;
>   replacement tbl_assembly%ROWTYPE;
> begin
>   for compound in execute 'select
>           *
>       from
>           tbl_assembly
>       where
>           component_id in (select item_id from tbl_assembly)'
>   loop
>       for replacement in select
>               *
>           from
>               tbl_assembly
>           where
>               item_id = compound.component_id
>       loop
>           insert into tbl_assembly values
>           (
>               compound.item_id,
>               replacement.component_id,
>               compound.quantity * replacement.quantity
>           );
>       end loop;
>       delete from
>           tbl_assembly
>       where
>           item_id = compound.item_id
>           and component_id = compound.component_id;
>   end loop;
>   return 1;
> end;
> $$ language plpgsql;
> --
> --


Hi All,


Doug, thanks for the post.  It got me started in the right direction.
Below is what I ended up with for a function.  (I still need to convert
it to a trigger.)


A couple of items that I learned while working on this that will
hopefully help someone else.


You can use the FOUND variable with an exit.  Instead of
   IF NOT FOUND THEN
     EXIT;
   END IF;
you can use
   EXIT WHEN NOT FOUND;
which IMHO reads better and is more concise.  Perhaps the gurus can
comment on true equivency.


You can use the FOUND variable in a WHILE LOOP.
PERFORM column
   FROM table
  WHERE expression;
WHILE FOUND LOOP
   <statements>
       "
   PERFORM column
      FROM table
     WHERE expression;
END LOOP;
This technique although not as concise as an unconditional LOOP
using the EXIT WHEN statement does have the advantage of skipping
the loop alltogether when the condition is not met the first time.



CREATE OR REPLACE FUNCTION tf_unrecurse_assembly()
   RETURNS integer AS
$BODY$
   DECLARE
      rcrd_assembly RECORD;
      rcrd_component RECORD;
      v_quantity FLOAT4;
   BEGIN
--    Unconditional loop.
         PERFORM tbl_assembly.id
            FROM tbl_assembly
            LEFT OUTER JOIN tbl_item
              ON ( tbl_assembly.component_id = tbl_item.id )
           WHERE tbl_item.item_type = 'ASY';
      WHILE FOUND LOOP
--       Retrieve all of the assembly components that are assemblies themselves.
         FOR rcrd_assembly IN SELECT tbl_assembly.id,
                                     tbl_assembly.component_id,
                                     tbl_assembly.quantity
                                FROM tbl_assembly
                                LEFT OUTER JOIN tbl_item
                                  ON ( tbl_assembly.component_id = tbl_item.id )
                               WHERE tbl_item.item_type = 'ASY'
         LOOP
--          Retrieve the components of the current assembly.
            FOR rcrd_component IN SELECT tbl_assembly.id,
                                         tbl_assembly.component_id,
                                         tbl_assembly.quantity
                                    FROM tbl_assembly
                                   WHERE tbl_assembly.id =
rcrd_assembly.component_id
            LOOP
--             Check to see if this component already exists as part of the
current assembly.
               PERFORM tbl_assembly.id
                 WHERE tbl_assembly.id = rcrd_assembly.id
                   AND tbl_assembly.component_id = rcrd_component.component_id;
               IF FOUND THEN
--                Add the new quantity and update the record.
                  SELECT tbl_assembly.quantity + ( rcrd_assembly.quantity *
rcrd_component.quantity ) INTO v_quantity
                   WHERE tbl_assembly.id = rcrd_assembly.id
                     AND tbl_assembly.component_id = rcrd_component.component_id;
                  UPDATE tbl_assembly
                     SET quantity = v_quantity
                   WHERE tbl_assembly.id = rcrd_assembly.id
                     AND tbl_assembly.component_id = rcrd_component.component_id;
               ELSE
                  INSERT INTO tbl_assembly
                              ( id,
                                component_id,
                                quantity )
                       VALUES ( rcrd_assembly.id,
                                rcrd_component.component_id,
                                rcrd_assembly.quantity *
rcrd_component.quantity );
               END IF;
            END LOOP;
            DELETE
              FROM tbl_assembly
             WHERE tbl_assembly.id = rcrd_assembly.id
               AND tbl_assembly.component_id = rcrd_assembly.component_id;
         END LOOP;
         PERFORM tbl_assembly.id
            FROM tbl_assembly
            LEFT OUTER JOIN tbl_item
              ON ( tbl_assembly.component_id = tbl_item.id )
           WHERE tbl_item.item_type = 'ASY';
--         EXIT WHEN NOT FOUND;
      END LOOP;
      RETURN 1;
   END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Kind Regards,
Keith