Thread: rownum
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? __________________________________________________ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com
Chester, > 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. -- Josh Berkus Aglio Database Solutions San Francisco
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
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?
-- Richard Huxton
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
Joe Conway <mail@joeconway.com> writes:
> 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.
Considering that it can trivially be done on the client side, I think it
would be a hard sell to put such a wart into the protocol ...
regards, tom lane
> Considering that it can trivially be done on the client side, I think > it would be a hard sell to put such a wart into the protocol ... having used Oracle for many years, I cannot think of a project in which I did not use rownum, including within procedures and triggers and subqueries. can usually be easily done - loop within loop - but more easily and bugfree as column within select. by "wart" do you mean aesthetically or code-wise? naively, it seems like pg is already keeping a row counter to implement limit. __________________________________________________ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com
Perhaps you are using ROWNUM like LIMIT/OFFSET... http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=queries-limit.html I hope this helps... chester c young wrote: > > > Considering that it can trivially be done on the client side, I think > > it would be a hard sell to put such a wart into the protocol ... > > having used Oracle for many years, I cannot think of a project in which > I did not use rownum, including within procedures and triggers and > subqueries. can usually be easily done - loop within loop - but more > easily and bugfree as column within select. > > by "wart" do you mean aesthetically or code-wise? naively, it seems > like pg is already keeping a row counter to implement limit.