Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
2006/4/8, Tom Lane [EMAIL PROTECTED]: 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn) ?
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. 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
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn) ?
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
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
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 [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)
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
On 4/8/06, Jan Wieck [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
Jan Wieck [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
On 4/8/06, Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
On 4/8/06, David Fetter [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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 I would _love_ to see rownums in PostgreSQL :) Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
