Re: recursive processing

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
>