Re: recursive processing - Mailing list pgsql-novice

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,
>
> 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
>