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(at)yahoo(dot)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 bar OFFSET 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