Content-Type: text/plain;
  charset="utf-8"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Tom Lane <tgl@sss.pgh.pa.us>, Josh Berkus <josh@agliodbs.com>
Subject: Re: rownum
Date: Fri, 14 Feb 2003 10:10:15 +0000
User-Agent: KMail/1.4.3
Cc: chester c young <chestercyoung@yahoo.com>,
	pgsql-sql@postgresql.org
References: <20030214043800.99417.qmail@web12707.mail.yahoo.com>
	<200302132056.20122.josh@agliodbs.com>
	<4353.1045200034@sss.pgh.pa.us>
In-Reply-To: <4353.1045200034@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302141010.15271.dev@archonet.com>

On Friday 14 Feb 2003 5:20 am, Tom Lane wrote:
>
> 	select nextval('rownum'), * from (select ... order by ...) sub;
>
> The overhead of using a sequence for this is pretty annoying.  It would
> be a simple matter to write a C function that emits sequential values
> without any database access (see pg_stat_get_backend_idset() for some
> inspiration).  But you'd still need the subselect to avoid getting
> re-sorted.  AFAICS any rownum() function that doesn't behave like that
> is a flat violation of the SQL standard...

Could you not build a wrapper function something like:

SELECT with_rownum('SELECT ...');

Where the function returns SETOF RECORD or similar - just fetch rows from t=
he=20
select and prepend a pg_rownum column?

--=20
  Richard Huxton