row_number() for Postgres before 8.4

Posted by sergey - 10/07/09 at 04:07:31 pm

Unfortunately i found a solution for this problem only this week, after the new postgres version 8.4 was already released. In the new version this function is already implemented along with many other functions called “window functions“.
In any way, for everyone that still uses Postgress version less then 8.4, here is my solution for this -


CREATE OR REPLACE FUNCTION counter(int) RETURNS INT AS $$
return $_[0] + $_SHARED{counter}++; $$
LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_counter() RETURNS INT AS $$
return $_SHARED{counter}++;
$$
immutable
LANGUAGE plperl;

create or replace function get_row_number() returns integer as
’select counter(get_counter()*-1);’
language sql

After declaring this set of functions you will be able to run queries like this -

select get_row_number(),* from table...

I think this solution is better then creating temporary sequences and using nextval(’rownum’).

Your comments are appreciated