Re: Numbering rows

Mark Morgan Lloyd wrote:
> Is there an easy way to assign a sequential number, possibly based on an
> arbitrary minimum (typically 0 or 1) to each row of an ordered result
> set, or do I have to work with explicit sequences?
>
> I need to do quite a lot of maths on successive rows, extracting numeric
> and timestamp differences hence rates of change. I've typically been
> doing it manually or in a spreadsheet but there has to be a better way
> e.g. by a join on offset row numbers.

PERL can remember variables in your session.  Here's a function I wrote
that sets a "global" variable in PL/PERL:

----------
CREATE OR REPLACE FUNCTION "public"."global_var_set"
   (in_key varchar, in_value bigint)
RETURNS bigint AS
$body$
my ($key, $value) = @_;
$_SHARED{$key} = $value;
return $value;
$body$
LANGUAGE 'plperl' VOLATILE RETURNS NULL ON NULL INPUT;
----------

Then, later you can read that global variable with another function like
this:

----------
CREATE OR REPLACE FUNCTION "public"."global_var_get"
(in_key varchar)
RETURNS bigint AS
$body$
my ($key) = @_;
return $_SHARED{$key} ? $_SHARED{$key} : 0;
$body$
LANGUAGE 'plperl' VOLATILE RETURNS NULL ON NULL INPUT;
----------

Perhaps you can use PL/PERL and a function like these to modify "global"
variables that you can increment as you do your select.  Something like:

    SELECT global_var_set(0);

    SELECT global_var_inc() AS row_counter, *
    FROM datatable
    ORDER BY whatever;

Just an idea.

-- Dante

----------
D. Dante Lorenso
dante@lorenso.com