Rownum in postgresql

If you have a unique key, you may use COUNT(*) OVER ( ORDER BY unique_key ) as ROWNUM

SELECT t.*, count(*) OVER (ORDER BY k ) ROWNUM 
FROM yourtable t;

| k |     n | rownum |
|---|-------|--------|
| a | TEST1 |      1 |
| b | TEST2 |      2 |
| c | TEST2 |      3 |
| d | TEST4 |      4 |

DEMO

I think it's possible to mimic Oracle rownum using temporary sequences.

create or replace function rownum_seq() returns text as $$
select concat('seq_rownum_',replace(uuid_generate_v4()::text,'-','_'));
$$ language sql immutable;

create or replace function rownum(r record, v_seq_name text default rownum_seq()) returns bigint as $$
declare 
begin
    return nextval(v_seq_name);
exception when undefined_table then
    execute concat('create temporary sequence ',v_seq_name,' minvalue 1 increment by 1');
    return nextval(v_seq_name);
end;
$$ language plpgsql volatile;

Demo:

select ccy_code,rownum(a.*) from (select ccy_code from currency order by ccy_code desc) a where rownum(a.*)<10;

Gives:

ZWD 1
ZMK 2
ZBH 3
ZAR 4
YUN 5
YER 6
XXX 7
XPT 8
XPF 9

Explanations:

Function rownum_seq() is immutable, called only once by PG in a query, so we get the same unique sequence name (even if the function is called thousand times in the same query)

Function rownum() is volatile and called each time by PG (even in a where clause)

Without r record parameter (which is unused), the function rownum() could be evaluated too early. That's the tricky point. Imagine, the following rownum() function:

create or replace function rownum(v_seq_name text default rownum_seq()) returns bigint as $$
declare 
begin
    return nextval(v_seq_name);
exception when undefined_table then
    execute concat('create temporary sequence ',v_seq_name,' minvalue 1 increment by 1');
    return nextval(v_seq_name);
end;
$$ language plpgsql volatile;


explain select ccy_code,rownum() from (select ccy_code from currency order by ccy_code desc) a where rownum()<10

Sort  (cost=56.41..56.57 rows=65 width=4)
  Sort Key: currency.ccy_code DESC
  ->  Seq Scan on currency  (cost=0.00..54.45 rows=65 width=4)
        Filter: (rownum('649aec1a-d512-4af0-87d8-23e8d8a9d982'::text) < 10)

PG apply the filter before the order. Damned! With the first unused parameter, we force PG to order before filter:

explain select * from (select ccy_code from currency order by ccy_code desc) a where rownum(a.*)<10;

Subquery Scan on a  (cost=12.42..64.36 rows=65 width=4)
  Filter: (rownum(a.*, 'seq_rownum_43b5c67f_dd64_4191_b29c_372061c848d6'::text) < 10)
  ->  Sort  (cost=12.42..12.91 rows=196 width=4)
        Sort Key: currency.ccy_code DESC
        ->  Seq Scan on currency  (cost=0.00..4.96 rows=196 width=4)

Pros:

• works as an expression or in a where clause
• easy to use: just pass the first record.* you have in the from

Cons:

• a temporary sequence is created for each rownum() encountered, but it is removed when session ends.
• performance (to discuss, row_number() over () versus nextval)