Thread: using a sequence as the functional equivalent to Oracle rownum

I'm trying to use a temporary sequence to duplicate the functionality of
the Oracle rownum pseudo-column
as suggested by Scott Marlow in the archives:
http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php.

The Oracle based application I'm porting to PostgreSQL used  rownum to
select the 'next' block of rows to
process by specifying a where clause with something like " where
rownum>x and rownum<y "

My basic PostgreSQL query is:

drop sequence rownum ;
create temp sequence rownum;

select B.rownum , B.id from
(select nextval('rownum') as rownum, A.*  from
(select distinct id  from ... where ... order by ... DESC
) as A
) as B
where id>0

This basic query produces the following result set:
rownum     id
--------+---------
      1       10038
      2       10809
      3       10810
      4       22549
      5       23023

However, if I add a where clause referencing rownum for example: where
id>0 and rownum>0
I get the following:

rownum   id
-------+---------
  11        10038
  12        10809
  13        10810
  14        22549
  15        23023

It appears as if rownum has been incremented as a result of three passes
over the five row result set.

Can someone explain what is going on?  And more to to point, if this is
expected behavior, is there a standard PostgreSQL way to select a
'block' of rows from a result set based on row number?

Thanks,
-=bill

"Wm.A.Stafford" <stafford@marine.rutgers.edu> writes:
> ... is there a standard PostgreSQL way to select a
> 'block' of rows from a result set based on row number?

LIMIT/OFFSET might be what you are looking for --- it's certainly far
less klugy than a temporary sequence.

            regards, tom lane

Wm.A.Stafford wrote:
> I'm trying to use a temporary sequence to duplicate the functionality
> of the Oracle rownum pseudo-column
> as suggested by Scott Marlow in the archives:
> http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php.
>
> The Oracle based application I'm porting to PostgreSQL used  rownum to
> select the 'next' block of rows to
> process by specifying a where clause with something like " where
> rownum>x and rownum<y "
>
> My basic PostgreSQL query is:
>
> drop sequence rownum ;
> create temp sequence rownum;
>
> select B.rownum , B.id from
> (select nextval('rownum') as rownum, A.*  from
> (select distinct id  from ... where ... order by ... DESC
> ) as A
> ) as B
> where id>0
>
> This basic query produces the following result set:
> rownum     id
> --------+---------
>      1       10038
>      2       10809
>      3       10810
>      4       22549
>      5       23023
>
> However, if I add a where clause referencing rownum for example: where
> id>0 and rownum>0
> I get the following:
>
> rownum   id
> -------+---------
>  11        10038
>  12        10809
>  13        10810
>  14        22549
>  15        23023
>
> It appears as if rownum has been incremented as a result of three
> passes over the five row result set.
>
> Can someone explain what is going on?  And more to to point, if this
> is expected behavior, is there a standard PostgreSQL way to select a
> 'block' of rows from a result set based on row number?
>
> Thanks,
> -=bill
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

I have done this using limit and offset like the following
select * from foo order by bar limit 10 offset 50;--giving the 10 rows
from position 51 onwards (offset is zero based)

Oisin