Using Postgres, I am trying to recursively calculate the "average" progress values to the "n" depth for parent / child / descendants items. Parent Items have a progress calculation of "PARENT" and children have a progressCalculation of "SELF"; however, children can also be parents having a progressCalculation of "PARENT" and in turn have their own children with progressCalculation of "SELF" and so on and so forth . . .
Ultimately the data set should look like the following:
<table border="1" width="100%">
<tr>
<td>itemId</td>
<td>title</td>
<td>progress</td>
<td>progressCalculation</td>
<td>parentItemId</td>
</tr>
<tr style="background-color:blue;color:white">
<td>1</td>
<td>Item 1</td>
<td>87.5</td>
<td>PARENT</td>
<td></td>
</tr>
<tr>
<td>2</td>
<td>Item 1.1</td>
<td>100</td>
<td>SELF</td>
<td>1</td>
</tr>
<tr>
<td>3</td>
<td>Item 1.2</td>
<td>100</td>
<td>SELF</td>
<td>1</td>
</tr>
<tr>
<td>4</td>
<td>Item 1.3</td>
<td>100</td>
<td>SELF</td>
<td>1</td>
</tr>
<tr style="background-color:blue;color:white">
<td>5</td>
<td>Item 1.4</td>
<td>50</td>
<td>PARENT</td>
<td>1</td>
</tr>
<tr>
<td>6</td>
<td>Item 1.4.1</td>
<td>50</td>
<td>SELF</td>
<td>5</td>
</tr>
<tr>
<td>7</td>
<td>Item 1.4.2</td>
<td>50</td>
<td>SELF</td>
<td>5</td>
</tr>
</table>I'm not 100% sure if a recursive query will be good for this situation, any help would be GREATLY appreciated.