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