X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 49BCE474E61 for ; Fri, 14 Feb 2003 10:44:21 -0500 (EST) Received: from joeconway.com (unknown [63.210.180.150]) by postgresql.org (Postfix) with ESMTP id 8DFDA474E5C for ; Fri, 14 Feb 2003 10:44:20 -0500 (EST) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.0.4) with ESMTP-TLS id 1611841; Fri, 14 Feb 2003 08:19:28 -0800 Message-ID: <3E4D0E66.1030406@joeconway.com> Date: Fri, 14 Feb 2003 07:42:30 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: Tom Lane , Josh Berkus , chester c young , pgsql-sql@postgresql.org Subject: Re: rownum References: <20030214043800.99417.qmail@web12707.mail.yahoo.com> <200302132056.20122.josh@agliodbs.com> <4353.1045200034@sss.pgh.pa.us> <200302141010.15271.dev@archonet.com> In-Reply-To: <200302141010.15271.dev@archonet.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/332 X-Sequence-Number: 11730 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