From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: chester c young <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: rownum
Date: Fri, 14 Feb 2003 00:20:34 -0500
Message-ID: <4353.1045200034@sss.pgh.pa.us> (raw)
In-Reply-To: <200302132056.20122.josh@agliodbs.com>
References: <20030214043800.99417.qmail@web12707.mail.yahoo.com>
<200302132056.20122.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com, chestercyoung@yahoo.com
Subject: Re: rownum
In-Reply-To: <4353.1045200034@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox