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
