Re: Enumerating a row set

On Thu, Mar 26, 2009 at 06:42:45PM -0400, George Sakkis wrote:
> Hi all,
>
> Is there a function similiar to Python's enumerate() [1] ? Searching
> the docs didn't reveal any relevant builtin but I hope it's doable in
> pgsql. Ideally I'd like a function that can be used as:
>
> SELECT e.i, e.col1, e.col2
> FROM enumerate(some_table, 'i') e
> LIMIT 10

In PostgreSQL 8.4, you'll be able to do:

SELECT
row_number() OVER (ORDER BY col1) AS i,
e.col1,
e.col2,
...
FROM ...

Until then, there are some ugly, fragile workarounds with
generate_series() and/or temp sequences.

Cheers,
David.
>
> i col1 col2
> =========
> 0 ... ...
> 1 ... ...
> ... ... ...
> 9 ... ...
>
> Also ideally it should work on any rowset (e.g. nested select), not
> just on concrete tables.
>
> Thanks in advance,
> George
>
> [1] http://docs.python.org/library/functions.html#enumerate
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate