pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrownum
8+ messages / 6 participants
[nested] [flat]
* rownum
@ 2003-02-14 04:38 chester c young <chestercyoung@yahoo.com>
2003-02-14 04:56 ` Re: rownum Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: chester c young @ 2003-02-14 04:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: rownum
2003-02-14 04:38 rownum chester c young <chestercyoung@yahoo.com>
@ 2003-02-14 04:56 ` Josh Berkus <josh@agliodbs.com>
2003-02-14 05:20 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2003-02-14 04:56 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: rownum
2003-02-14 04:38 rownum chester c young <chestercyoung@yahoo.com>
2003-02-14 04:56 ` Re: rownum Josh Berkus <josh@agliodbs.com>
@ 2003-02-14 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-14 10:10 ` Re: rownum Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-02-14 05:20 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: rownum
2003-02-14 04:38 rownum chester c young <chestercyoung@yahoo.com>
2003-02-14 04:56 ` Re: rownum Josh Berkus <josh@agliodbs.com>
2003-02-14 05:20 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-14 10:10 ` Richard Huxton <dev@archonet.com>
2003-02-14 15:42 ` Re: rownum Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2003-02-14 10:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: rownum
2003-02-14 04:38 rownum chester c young <chestercyoung@yahoo.com>
2003-02-14 04:56 ` Re: rownum Josh Berkus <josh@agliodbs.com>
2003-02-14 05:20 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
2003-02-14 10:10 ` Re: rownum Richard Huxton <dev@archonet.com>
@ 2003-02-14 15:42 ` Joe Conway <mail@joeconway.com>
2003-02-14 16:21 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Joe Conway @ 2003-02-14 15:42 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: rownum
2003-02-14 04:38 rownum chester c young <chestercyoung@yahoo.com>
2003-02-14 04:56 ` Re: rownum Josh Berkus <josh@agliodbs.com>
2003-02-14 05:20 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
2003-02-14 10:10 ` Re: rownum Richard Huxton <dev@archonet.com>
2003-02-14 15:42 ` Re: rownum Joe Conway <mail@joeconway.com>
@ 2003-02-14 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-15 04:54 ` Re: rownum chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-02-14 16:21 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Richard Huxton <dev@archonet.com>; Josh Berkus <josh@agliodbs.com>; chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: rownum
2003-02-14 04:38 rownum chester c young <chestercyoung@yahoo.com>
2003-02-14 04:56 ` Re: rownum Josh Berkus <josh@agliodbs.com>
2003-02-14 05:20 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
2003-02-14 10:10 ` Re: rownum Richard Huxton <dev@archonet.com>
2003-02-14 15:42 ` Re: rownum Joe Conway <mail@joeconway.com>
2003-02-14 16:21 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-15 04:54 ` chester c young <chestercyoung@yahoo.com>
2003-02-17 16:25 ` Re: rownum Terry Yapt <pgsql@technovell.com>
0 siblings, 1 reply; 8+ messages in thread
From: chester c young @ 2003-02-15 04:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: rownum
2003-02-14 04:38 rownum chester c young <chestercyoung@yahoo.com>
2003-02-14 04:56 ` Re: rownum Josh Berkus <josh@agliodbs.com>
2003-02-14 05:20 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
2003-02-14 10:10 ` Re: rownum Richard Huxton <dev@archonet.com>
2003-02-14 15:42 ` Re: rownum Joe Conway <mail@joeconway.com>
2003-02-14 16:21 ` Re: rownum Tom Lane <tgl@sss.pgh.pa.us>
2003-02-15 04:54 ` Re: rownum chester c young <chestercyoung@yahoo.com>
@ 2003-02-17 16:25 ` Terry Yapt <pgsql@technovell.com>
0 siblings, 0 replies; 8+ messages in thread
From: Terry Yapt @ 2003-02-17 16:25 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-02-17 16:25 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-14 04:38 rownum chester c young <chestercyoung@yahoo.com>
2003-02-14 04:56 ` Josh Berkus <josh@agliodbs.com>
2003-02-14 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-14 10:10 ` Richard Huxton <dev@archonet.com>
2003-02-14 15:42 ` Joe Conway <mail@joeconway.com>
2003-02-14 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-15 04:54 ` chester c young <chestercyoung@yahoo.com>
2003-02-17 16:25 ` Terry Yapt <pgsql@technovell.com>
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