Skip site navigation (1) Skip section navigation (2)

Re: need ``row number``

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: need ``row number``
Date: 2004-09-24 14:20:13
Message-ID: 21339.1096035613@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-sql
Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> I am not convinced I'll need a SRF. I am not trying to
> calculate something that isn't there yet. I am just trying to
> join two views appropriately. I might have to employ some
> variant of Celko's integer helper table but I'm not sure how
> to proceed.

A fairly common hack for this is to use a sequence:

	create temp sequence tseq;
	select nextval('tseq'), * from (select .... order by ...) ss;

Note you must do any desired ORDER BY inside the subselect.  If it's
outside then it happens after computation of the row numbers, which
is exactly not what you want.

The major limitation of this is that you need to do some auxiliary
operations to create or reset the semaphore before each query.

There are some related hacks in the archives that use PL functions
with private persistent state, instead of a sequence object.

			regards, tom lane

In response to

Responses

pgsql-sql by date

Next:From: Vladimir S. TikhonjukDate: 2004-09-24 14:21:48
Subject: INDEX problem
Previous:From: Jennifer LeeDate: 2004-09-24 11:50:36
Subject: select column by position

pgsql-general by date

Next:From: Tom LaneDate: 2004-09-24 14:32:16
Subject: Re: Large arrays give long lag on server side before command executes
Previous:From: Tom LaneDate: 2004-09-24 14:02:19
Subject: Re: How do I disable: Adding missing FROM-clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group