To: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: need ``row number`` 
In-reply-to: <20040924092117.A1195@hermes.hilbert.loc> 
References: <s1530b77.094@mail.tpchd.org>
	<20040924092117.A1195@hermes.hilbert.loc>
Comments: In-reply-to Karsten Hilbert <Karsten.Hilbert@gmx.net>
	message dated "Fri, 24 Sep 2004 09:21:17 +0200"
Date: Fri, 24 Sep 2004 10:20:13 -0400
Message-ID: <21339.1096035613@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Karsten Hilbert <Karsten.Hilbert@gmx.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