Re: 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 <jmdiazlr(at)yahoo(dot)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(at)Yahoo(dot)com #