How can you generate a counter for ordered sets?

From: Christopher Maier <maier(at)email(dot)unc(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: How can you generate a counter for ordered sets?
Date: 2007-05-17 13:19:48
Message-ID: 4E9181C4-E46A-4D0F-8A35-1EE1C64AC48E@email.unc.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Broersma Jr 2007-05-17 13:38:26 Re: Whole-row comparison ?
Previous Message christian.roche.ext 2007-05-17 12:56:08 Whole-row comparison ?