On Sat, Apr 08, 2006 at 12:46:06PM -0400, Tom Lane wrote:
> Juan Manuel Diaz Lara <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
