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

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.