To: Josh Berkus <josh@agliodbs.com>
Cc: chester c young <chestercyoung@yahoo.com>,
	pgsql-sql@postgresql.org
Subject: Re: rownum 
In-reply-to: <200302132056.20122.josh@agliodbs.com> 
References: <20030214043800.99417.qmail@web12707.mail.yahoo.com>
	<200302132056.20122.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Thu, 13 Feb 2003 20:56:20 -0800"
Date: Fri, 14 Feb 2003 00:20:34 -0500
Message-ID: <4353.1045200034@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Josh Berkus <josh@agliodbs.com> writes:
>> sorry about this - braindead and cannot find in doc.  what's pg's
>> rownum pseudo-column or function name that returns the record number of
>> a set?

> There isn't one, unless there's something in /contrib that you can build.

Right now the only way I've heard of is to use a sequence, for example

	create temp sequence rownum;

	select nextval('rownum'), ... from ...;

	drop sequence rownum;

This is a hack, and it will fail if the SELECT involves any sort
specification (not only ORDER BY, but DISTINCT) because the nextval()s
will be computed before sorting.  You can get around that with

	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...

			regards, tom lane