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

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

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.

I found this via Google:

http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/

--
Guy Rouillier

Hello,

There is also a funny approach here with custom operators and variables
that could be adapted to build a rownum functioanlity in functions
(look for Vladimir)

http://archives.postgresql.org/pgsql-hackers/2008-11/msg00048.php

not very clean, but apparently fast ....

Marc Mamin

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Guy Rouillier
Sent: Friday, March 27, 2009 2:54 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.

I found this via Google:

http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-
in-one-query/

--
Guy Rouillier

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Mar 26, 2009 at 8:55 PM, David Fetter <david@fetter.org> wrote:

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

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

Good news! Better late than never :)

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

That's exactly my impression based on the other replies and searching
for "postgresql rownum" online.

Thanks,
George