From: | "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com> |
---|---|
To: | Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk> |
Cc: | pgsql-general(at)PostgreSQL(dot)org |
Subject: | Re: Numbering rows |
Date: | 2008-10-15 20:18:54 |
Message-ID: | 48F6502E.4050808@lorenso.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)lorenso(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David Wall | 2008-10-15 21:01:12 | Get PG version using JDBC? |
Previous Message | Mark Morgan Lloyd | 2008-10-15 20:18:08 | Re: Numbering rows |