Обсуждение: 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