pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Rodrigo De León <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: How can you generate a counter for ordered sets?
Date: Tue, 19 Jun 2007 04:06:49 -0000
Message-ID: <1182226009.045074.165580@u2g2000hsc.googlegroups.com> (raw)
In-Reply-To: <4E9181C4-E46A-4D0F-8A35-1EE1C64AC48E@email.unc.edu>
References: <4E9181C4-E46A-4D0F-8A35-1EE1C64AC48E@email.unc.edu>

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




view thread (2+ messages)

Message-ID: <1182226009.045074.165580@u2g2000hsc.googlegroups.com>
Permalink:  ../1182226009.045074.165580@u2g2000hsc.googlegroups.com/
Also on:    postgresql.org/message-id/1182226009.045074.165580@u2g2000hsc.googlegroups.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rdeleonp@gmail.com
  Subject: Re: How can you generate a counter for ordered sets?
  In-Reply-To: <1182226009.045074.165580@u2g2000hsc.googlegroups.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox