Thread: Enumerating a row set
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. Thanks in advance, George [1] http://docs.python.org/library/functions.html#enumerate
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