Skip site navigation (1) Skip section navigation (2)

recursive processing

From: Keith Worthington <KeithW(at)NarrowPathInc(dot)com>
To: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: recursive processing
Date: 2005-02-17 03:23:15
Message-ID: 42140E23.3040609@NarrowPathInc.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


Responses

pgsql-novice by date

Next:From: Doug GorleyDate: 2005-02-17 06:02:36
Subject: Re: recursive processing
Previous:From: Oisin GlynnDate: 2005-02-17 00:08:54
Subject: Re: Front End Languages for PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group