pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can you generate a counter for ordered sets?
2+ messages / 2 participants
[nested] [flat]
* How can you generate a counter for ordered sets?
@ 2007-05-17 13:19 Christopher Maier <maier@email.unc.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Christopher Maier @ 2007-05-17 13:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How can you generate a counter for ordered sets?
@ 2007-06-19 04:06 Rodrigo De León <rdeleonp@gmail.com>
parent: Christopher Maier <maier@email.unc.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Rodrigo De León @ 2007-06-19 04:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-06-19 04:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-17 13:19 How can you generate a counter for ordered sets? Christopher Maier <maier@email.unc.edu>
2007-06-19 04:06 ` Rodrigo De León <rdeleonp@gmail.com>
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