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

On May 17, 8:19 am, m(dot)(dot)(dot)(at)email(dot)unc(dot)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