Re: How to implement oracle like rownum(function or seudocolumn) ?

On Sat, Apr 08, 2006 at 12:46:06PM -0400, Tom Lane wrote:
> Juan Manuel Diaz Lara <jmdiazlr@yahoo.com> writes:
> > I need a rownum column, like Oracle. I have searched the mailing lists
> > and I don't see a satisfactory solution, so I was wondering write a
> > UDF to implement it, the requirements are:
> 
> Try keeping a counter in fcinfo->flinfo->fn_extra.

Is this close to being correct?

Datum
rownum(PG_FUNCTION_ARGS)
{   int64  *row_counter;
   if (fcinfo->flinfo->fn_extra == NULL) {       row_counter = (int64 *)MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
                                           sizeof(int64));       *row_counter = 0;       fcinfo->flinfo->fn_extra =
row_counter;  }
 
   row_counter = fcinfo->flinfo->fn_extra;
   PG_RETURN_INT64(++(*row_counter));
}

> >   3. And more important, need to be called in the right place when
>  called from subquerys:
> 
> Don't expect miracles in this department.  The planner will evaluate the
> function where it sees fit...

Would OFFSET 0 be the workaround in this case?

SELECT rownum(), *
FROM (SELECT rownum() AS f_rownum, id AS f_id FROM foo) AS f,    (SELECT rownum() AS b_rownum, id AS b_id FROM bar) AS
b;rownum| f_rownum | f_id  | b_rownum | b_id  
 
--------+----------+-------+----------+-------     1 |        1 | foo-1 |        1 | bar-1     2 |        2 | foo-1 |
    2 | bar-2     3 |        3 | foo-2 |        3 | bar-1     4 |        4 | foo-2 |        4 | bar-2     5 |        5
|foo-3 |        5 | bar-1     6 |        6 | foo-3 |        6 | bar-2
 
(6 rows)

SELECT rownum(), *
FROM (SELECT rownum() AS f_rownum, id AS f_id FROM foo OFFSET 0) AS f,    (SELECT rownum() AS b_rownum, id AS b_id FROM
barOFFSET 0) AS b;rownum | f_rownum | f_id  | b_rownum | b_id  
 
--------+----------+-------+----------+-------     1 |        1 | foo-1 |        1 | bar-1     2 |        1 | foo-1 |
    2 | bar-2     3 |        2 | foo-2 |        1 | bar-1     4 |        2 | foo-2 |        2 | bar-2     5 |        3
|foo-3 |        1 | bar-1     6 |        3 | foo-3 |        2 | bar-2
 
(6 rows)

-- 
Michael Fuhr