pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Joe Conway <mail@joeconway.com>
To: Richard Huxton <dev@archonet.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 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 07:42:30 -0800
Message-ID: <3E4D0E66.1030406@joeconway.com> (raw)
In-Reply-To: <200302141010.15271.dev@archonet.com>
References: <20030214043800.99417.qmail@web12707.mail.yahoo.com>
	<200302132056.20122.josh@agliodbs.com>
	<4353.1045200034@sss.pgh.pa.us>
	<200302141010.15271.dev@archonet.com>

Richard Huxton wrote:
> 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 the 
> select and prepend a pg_rownum column?
> 

I've played with this a bit in the past, and concluded that the best way 
to do it (if in fact you agree it should be done at all), would be to 
add a rownum pseudo column as the results are projected from the backend 
to the frontend. I think this would require a change to the FE/BE 
protocol, which we've talked about doing for 7.4.

Joe




view thread (8+ messages)  latest in thread

Message-ID: <3E4D0E66.1030406@joeconway.com>
Permalink:  ../3E4D0E66.1030406@joeconway.com/
Also on:    postgresql.org/message-id/3E4D0E66.1030406@joeconway.com

 · 

reply
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: mail@joeconway.com, dev@archonet.com, tgl@sss.pgh.pa.us, josh@agliodbs.com, chestercyoung@yahoo.com
  Subject: Re: rownum
  In-Reply-To: <3E4D0E66.1030406@joeconway.com>

* 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