Re: How to implement oracle like rownum(function or seudocolumn)

On 4/8/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> A function implemented as per Michael's example would not give the
> results that I think people would expect for
>
> SELECT rownum(), * FROM foo ORDER BY whatever;
>

Yep, the query would have to be rewritten similar to Oracle's:

SELECT rownum(), * FROM (SELECT * FROM foo ORDER BY whatever);

IIRC, processing-wise, rownum and order-by processing is handled as follows:

SELECT id, whatever FROM foo WHERE rownum <= 10 ORDER BY id;

is the same as PostgreSQL's

SELECT id, whatever FROM (SELECT id, whatever FROM foo LIMIT 10) ORDER BY id;

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324