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