Re: How can you generate a counter for ordered sets?

On May 17, 8:19 am, m...@email.unc.edu (Christopher Maier) wrote:
> Conceptually, all the exons for a given gene form a set, ordered by
> their "start" attribute.  I need to add a new integer column to the
> table to store a counter for each exon that indicates their position
> in this ordering.
>
> Is there a straightforward way to populate this new position column?
> I've done an iterative solution in PL/pgSQL which works (slowly), but
> I was wondering if there was a more efficient way to do this kind of
> thing.

SELECT * FROM EXON;
id | gene | start | stop
----+------+-------+------ 1 |    1 |     1 |   10 2 |    2 |    11 |   20 3 |    3 |    21 |   30

SELECT
ID, GENE, START, STOP
, GENERATE_SERIES(START, STOP) AS POSITION
FROM EXON;
id | gene | start | stop | position
----+------+-------+------+---------- 1 |    1 |     1 |   10 |        1 1 |    1 |     1 |   10 |        2 1 |    1 |
  1 |   10 |        3 1 |    1 |     1 |   10 |        4 1 |    1 |     1 |   10 |        5 1 |    1 |     1 |   10 |
    6 1 |    1 |     1 |   10 |        7 1 |    1 |     1 |   10 |        8 1 |    1 |     1 |   10 |        9 1 |    1
|    1 |   10 |       10 2 |    2 |    11 |   20 |       11 2 |    2 |    11 |   20 |       12 2 |    2 |    11 |   20
|      13 2 |    2 |    11 |   20 |       14 2 |    2 |    11 |   20 |       15 2 |    2 |    11 |   20 |       16 2 |
 2 |    11 |   20 |       17 2 |    2 |    11 |   20 |       18 2 |    2 |    11 |   20 |       19 2 |    2 |    11 |
20|       20 3 |    3 |    21 |   30 |       21 3 |    3 |    21 |   30 |       22 3 |    3 |    21 |   30 |       23 3
|   3 |    21 |   30 |       24 3 |    3 |    21 |   30 |       25 3 |    3 |    21 |   30 |       26 3 |    3 |    21
|  30 |       27 3 |    3 |    21 |   30 |       28 3 |    3 |    21 |   30 |       29 3 |    3 |    21 |   30 |
30