From: =?iso-8859-1?q?Rodrigo_De_Le=F3n?= <rdeleonp@gmail.com>
Subject: Re: How can you generate a counter for ordered sets?
Date: Tue, 19 Jun 2007 04:06:49 -0000
Organization: http://groups.google.com
Lines: 57
Message-ID: <1182226009.045074.165580@u2g2000hsc.googlegroups.com>
References: <4E9181C4-E46A-4D0F-8A35-1EE1C64AC48E@email.unc.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
In-Reply-To: <4E9181C4-E46A-4D0F-8A35-1EE1C64AC48E@email.unc.edu>
User-Agent: G2/1.0
Complaints-To: groups-abuse@google.com
Injection-Info: u2g2000hsc.googlegroups.com; posting-host=201.224.12.243;
	posting-account=zGrw3Q0AAADU1m7dOiE5S6vTNtvJ7FdA
To: pgsql-sql@postgresql.org

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