Re: recursive processing
От | Doug Gorley |
---|---|
Тема | Re: recursive processing |
Дата | |
Msg-id | 4214337C.3060503@shaw.ca обсуждение исходный текст |
Ответ на | recursive processing (Keith Worthington <KeithW@NarrowPathInc.com>) |
Список | 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 >
В списке pgsql-novice по дате отправления: