Re: Using generate_series to create a unique ID in a query?

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Mon, Nov 12, 2007 at 03:11:50PM -0800, Sarah Dougherty wrote:
>> To recap with an example, the query below works fine, but how do I add a
>> series to it?

> generate_series will not help with this.
> try the sequence approach, or this:
> http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/

That's a fairly ugly/messy way of doing it.  If you're going to need a C
function anyway, why not just do it directly?  As in the attachment.

regression=# create function rownum() returns int as '/home/tgl/pgsql/rownum'
regression-# language c;
CREATE FUNCTION

One thing you have to watch out for is that per spec, ORDER BY happens
after evaluation of the SELECT's targetlist, and in fact PG will usually
do it that way if an explicit sort is needed.  So for example, this
works fine:

regression=# select rownum(),* from int8_tbl;
 rownum |        q1        |        q2
--------+------------------+-------------------
      1 |              123 |               456
      2 |              123 |  4567890123456789
      3 | 4567890123456789 |               123
      4 | 4567890123456789 |  4567890123456789
      5 | 4567890123456789 | -4567890123456789
(5 rows)

but this will not give the desired results:

regression=# select rownum(),* from int8_tbl order by q2;
 rownum |        q1        |        q2
--------+------------------+-------------------
      5 | 4567890123456789 | -4567890123456789
      3 | 4567890123456789 |               123
      1 |              123 |               456
      2 |              123 |  4567890123456789
      4 | 4567890123456789 |  4567890123456789
(5 rows)

You can work around it with a subselect:

regression=# select rownum(),* from (select * from int8_tbl order by q2) ss;
 rownum |        q1        |        q2
--------+------------------+-------------------
      1 | 4567890123456789 | -4567890123456789
      2 | 4567890123456789 |               123
      3 |              123 |               456
      4 |              123 |  4567890123456789
      5 | 4567890123456789 |  4567890123456789
(5 rows)

However, that bit of ugliness is enough to dissuade me from wanting to
put this into core PG ...

            regards, tom lane

#include "postgres.h"

#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

Datum rownum(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(rownum);

Datum
rownum(PG_FUNCTION_ARGS)
{
    int32       *ptr;

    ptr = (int32 *) fcinfo->flinfo->fn_extra;
    if (ptr == NULL)
    {
        /* First time through for the current query: allocate storage */
        fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
                                                      sizeof(int32));
        ptr = (int32 *) fcinfo->flinfo->fn_extra;
        /* ... and initialize counter */
        *ptr = 1;
    }
    else
        (*ptr)++;

    PG_RETURN_INT32(*ptr);
}