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

I am in the process of transitioning a bioinformatics database from  
one schema to another, and I have to do some "massaging" of the data  
in order to do it.

I have two tables, "gene" and "exon".  Exon has a many-to-one  
relationship with Gene.  The structure of the Gene table isn't  
important, but the Exon table looks like this:

CREATE TABLE exon(id SERIAL PRIMARY KEY,gene INTEGER REFERENCES gene(id),start INTEGER,stop INTEGER
);

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.

Thanks in advance,

Christopher Maier

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