Re: Enumerating a row set

On Mar 26, 2009, at 3:42 PM, 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
>
> i col1 col2
> =========
> 0 ... ...
> 1 ... ...
> ... ... ...
> 9 ... ...
>
> Also ideally it should work on any rowset (e.g. nested select), not
> just on concrete tables.

You're looking for what's called rownum in some other databases.

You can do it in postgresql with a temporary sequence, sometimes at
least:

abacus=# create temporary sequence bar;
CREATE SEQUENCE

abacus=# select setval('bar', 1, false);
setval
--------
1
(1 row)

abacus=# select nextval('bar'), baz from foo;
nextval | baz
---------+--------
1 | red
2 | yellow
3 | blue

Cheers,
Steve