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

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

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

Someone correct me if I'm wrong, but I was allways under the impression 
that Oracle's ROWNUM is a thing attached to a row in the final result 
set, whatever (possibly random) order that happens to have. Now a) this 
is something that IMHO belongs into the client or stored procedure code, 
b) if I am right, the code below will break as soon as an ORDER BY is 
added to the query and most importantly c) if a) cannot do the job, it 
indicates that the database schema or business process definition lacks 
some key/referential definition and is in need of a fix.

My humble guess is that c) is also the reason why the ANSI didn't find a 
ROWNUM desirable.


Jan


On 4/8/2006 1:26 PM, Michael Fuhr wrote:
> 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 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)
> 


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

On 4/8/06, Jan Wieck <JanWieck@yahoo.com> wrote:
> Someone correct me if I'm wrong, but I was allways under the impression
> that Oracle's ROWNUM is a thing attached to a row in the final result
> set, whatever (possibly random) order that happens to have. Now a) this
> is something that IMHO belongs into the client or stored procedure code,
> b) if I am right, the code below will break as soon as an ORDER BY is
> added to the query and most importantly c) if a) cannot do the job, it
> indicates that the database schema or business process definition lacks
> some key/referential definition and is in need of a fix.

Yes, a rownum is assigned at fetch time.

An example is the following PostgreSQL query:

SELECT id FROM sometable ORDER BY id LIMIT 5;

In Oracle-land is written as:

SELECT id FROM (SELECT id FROM sometable ORDER BY id) WHERE rownum <= 5;

> My humble guess is that c) is also the reason why the ANSI didn't find a
> ROWNUM desirable.

I believe this is a good assumption.


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

Jan Wieck <JanWieck@Yahoo.com> writes:
> My humble guess is that c) is also the reason why the ANSI didn't find a 
> ROWNUM desirable.

I've never understood what the conceptual model is for Oracle's rownum.
Where along the SQL operational pipeline (FROM / WHERE / GROUP BY /
aggregate / compute output columns / ORDER BY) is it supposed to be
computed?  To be useful for the often-requested purpose of nicely
labeling output with line numbers, it'd have to be assigned
post-ORDER-BY, but then it doesn't make any sense at all to use it in
WHERE, nor in sub-selects.

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;

unless the planner chances to do the ordering with an indexscan.
If it does it with a sort step then the rownums will be computed before
sorting :-(
        regards, tom lane

On 4/8/06, Tom Lane <tgl@sss.pgh.pa.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

On Sat, Apr 08, 2006 at 02:02:53PM -0400, Jan Wieck wrote:
> Someone correct me if I'm wrong, but I was allways under the impression 
> that Oracle's ROWNUM is a thing attached to a row in the final result 
> set, whatever (possibly random) order that happens to have. Now a) this 
> is something that IMHO belongs into the client or stored procedure code, 
> b) if I am right, the code below will break as soon as an ORDER BY is 
> added to the query and most importantly c) if a) cannot do the job, it 
> indicates that the database schema or business process definition lacks 
> some key/referential definition and is in need of a fix.
> 
> My humble guess is that c) is also the reason why the ANSI didn't find a 
> ROWNUM desirable.

Sadly, ANSI did just that.

http://troels.arvin.dk/db/rdbms/#select-limit
http://troels.arvin.dk/db/rdbms/#select-top-n
http://troels.arvin.dk/db/rdbms/#select-limit-offset

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!

On 4/8/06, David Fetter <david@fetter.org> wrote:
> Sadly, ANSI did just that.

Thanks for pointing that out... I'd been using LIMIT/OFFSET for so
long, I totally forgot about the standard :(


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

> 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

I would _love_ to see rownums in PostgreSQL :)

Chris

On Sat, Apr 08, 2006 at 03:04:40PM -0400, Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> > My humble guess is that c) is also the reason why the ANSI didn't find a 
> > ROWNUM desirable.
> 
> I've never understood what the conceptual model is for Oracle's rownum.
> Where along the SQL operational pipeline (FROM / WHERE / GROUP BY /
> aggregate / compute output columns / ORDER BY) is it supposed to be
> computed?  To be useful for the often-requested purpose of nicely
> labeling output with line numbers, it'd have to be assigned
> post-ORDER-BY, but then it doesn't make any sense at all to use it in
> WHERE, nor in sub-selects.

AFAIK rownum() is Oracle's solution to doing LIMIT ... OFFSET from
before those were ANSI. rownum() is applied as rows are leaving the
relevant node, which means you can't use rownum() in any part of a
SELECT statement other than the SELECT clause (you can't even use it in
a HAVING clause afaik, though I would think you should be able to). So,
if you want to actually do anything useful with rownum(), you have to
use it in a subquery and then operate at a higher level:

SELECT * FROM (SELECT rownum() AS row_number, * FROM table) z ORDER BY
row_number;

> 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;
> 
> unless the planner chances to do the ordering with an indexscan.
> If it does it with a sort step then the rownums will be computed before
> sorting :-(

I think you're right.

If people are that hot-to-trot about having Oracle compatable rownum()
in PostgreSQL, perhaps EnterpriseDB has some code they could share.
Though I think it'd be better to understand what people actually want
this info for. Personally I think having a rank function (or a complete
suite of analytic functions) would be far more useful.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

2006/4/8, Tom Lane <tgl@sss.pgh.pa.us>:

> I've never understood what the conceptual model is for Oracle's rownum.
> Where along the SQL operational pipeline (FROM / WHERE / GROUP BY /
> aggregate / compute output columns / ORDER BY) is it supposed to be
> computed?  To be useful for the often-requested purpose of nicely
> labeling output with line numbers, it'd have to be assigned
> post-ORDER-BY, but then it doesn't make any sense at all to use it in
> WHERE, nor in sub-selects.
>
> 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;
>
> unless the planner chances to do the ordering with an indexscan.
> If it does it with a sort step then the rownums will be computed before
> sorting :-(

I don't know about Oracle or ROW_NUM, but SQL apparently defines
ROW_NUMBER() OVER (..) (see
<url:http://en.wikipedia.org/wiki/Select_(SQL)#ROW_NUMBER.28.29_window_function>)

This gives a number for each output row, according to some ordering
(in SQL, one cannot do ORDER BY in a subquery AFAIK). If used in a
subquery, one can then of course use the resulting column in the WHERE
clause of the outer query:

SELECT * FROM ( SELECT   ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,   columns FROM tablename
) AS foo
WHERE rownumber <= 10

(example stolen from the Wikipedia article linked above).

--
Nicolas Barbier
http://www.gnu.org/philosophy/no-word-attachments.html