Re: increment row number function question - Mailing list pgsql-general

Matthew Peter <survivedsushi@yahoo.com> writes:
> Hello. I need a way to return an iterator result as a column.... eg,

> SELECT increment() as ii, some_col from some_tbl order by some_col desc limit 50;

>  ii | some_col
> ----+----------
>   1 |       zest
>   2 |       test
>   3 |       nest
>   4 |       fest
> [...]

You'd be a whole lot better off to attach the row numbers in your
client-side code.

Even if such a function existed, it would almost certainly not do what
you want in this query.  Per SQL spec, the SELECT target list is
logically supposed to be evaluated before the ORDER BY and LIMIT steps,
which means you'd get numbers associated with the physical ordering of
the rows not their some_col ordering.

You could possibly work around that problem with a sub-select, but at
some point you need to ask yourself whether it's not simpler to do a
presentation-oriented task like this in the client.

            regards, tom lane