PostgreSQL Hacks - How to implement oracle like rownum(function or seudocolumn) ?

Author

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

Juan Manuel Diaz Lara

2006-04-08, 1:27 pm

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:

1. Need to preserve state between calls to the function (the rowcount). Maybe using a MemoryContext with a lifespan equal to the executing query.
2. It should be called every time a new row is produced.
3. And more important, need to be called in the right place when called from subquerys:

Imagine one to man relationship between table A and tabla B. Then next query

SELECT s.id, s.rownum
FROM (SELECT a.id, rownum() AS rownum
FROM a) s,
b
WHERE a.id = b.id

In this case, the rownum function should behave as if it was an aggregated function, where the subquery is evaluated first and the results joined with table b. I think that a UDF could be evaluated last by the planner (when producing the final resultset
) so it won't give the intended result.

I looking for a general solution, Any ideas? .

Thanks.

Atte.

Juan Manuel Díaz Lara

---------------------------------
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.

Tom Lane

2006-04-08, 1:27 pm

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.

> 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...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Michael Fuhr

2006-04-08, 1:27 pm

On Sat, Apr 08, 2006 at 12:46:06PM -0400, Tom Lane wrote:
> Juan Manuel Diaz Lara <jmdiazlr@yahoo.com> writes:
>
> Try keeping a counter in fcinfo->flinfo->fn_extra.

Is this close to being correct?

Datum
rownum(PG_FUNCTION_A
RGS)
{
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));
}

> 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

Sponsored Links