From: Richard Huxton <dev@archonet.com>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-sql@postgresql.org
Subject: Re: Running tally
Date: Sat, 11 Oct 2003 19:43:38 +0100
User-Agent: KMail/1.5
References: <3F7E9956.30804@familyhealth.com.au>
In-Reply-To: <3F7E9956.30804@familyhealth.com.au>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200310111943.38792.dev@archonet.com>

On Saturday 04 October 2003 10:56, Christopher Kings-Lynne wrote:
> Hi guys,
>
> If I have a table that is just a single column full of numbers, how can
> I select all the rows from the table with a second column that is the
> running tally so far down the result set?
>
> eg:
>
> Num     Tally so far
> 0.3	0.3
> 1.2	1.5
> 2.0	3.5
> ...
>
> Does this require PL/PgSQL coding?  If so, how do you actually construct
> an arbitrary row for returning?  The docs are somewhat unclear on this.

Did you post this days ago, or is your/my clock wrong - it reads 2003-10-04 in 
my mailer?

Anyway, you could do it with a sub-query, but performance will be less than 
great with a large result-set.

SELECT * FROM runtot ;
 idx | num
-----+-----
   1 |  10
   2 |  20
   3 |  30

CREATE FUNCTION runtot_sum(int4) RETURNS int4 AS 'SELECT sum(num)::int4 FROM 
runtot WHERE idx <= $1' LANGUAGE 'SQL';

SELECT idx, num, runtot_sum(idx) FROM runtot ORDER BY idx;
 idx | num | runtot_sum
-----+-----+------------
   1 |  10 |         10
   2 |  20 |         30
   3 |  30 |         60


Procedural is the way to go if you have a large result set. Stephan Szabo has 
written some good notes on set-returning functions:
http://techdocs.postgresql.org/guides/SetReturningFunctions

Basically, define a type:

CREATE TYPE run_tot_type AS (
  a whatever,
  b whatever,
  run_tot whatever
);

Then:

CREATE FUNCTION my_run_tot() RETURNS SETOF run_tot_type AS...

Accumulate your values in a record-type variable and use RETURN NEXT to issue 
each row.

HTH
-- 
  Richard Huxton
  Archonet Ltd