Skip site navigation (1) Skip section navigation (2)

aggregate function ?

From: Raimon Fernandez <coder(at)montx(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: aggregate function ?
Date: 2007-05-15 20:56:37
Message-ID: 1A0BBE5C-362B-4696-8A17-458B44069B02@montx.com (view raw or flat)
Thread:
Lists: pgsql-novice
hi again,


Some days ago I asked for help, nobody replied, and after trying to  
do it in some way, I think aggregate function is the solution to my  
problem, but I found difficult to understand how it works ...

What I want to do is accumulate the value for each row and add it to  
the next:

a 100 100
b 50 150
c 25 175


My first approach was using a function/stored procedure, create a  
cursor, loop through it, and insert the values in a temporary table,  
that's why I was asking for those 'create table' before.

using a front-en application this is very easy, but I want to do it  
directly in the server, and also it's a great exercice to learn more  
about postgresql.

aggregate function



What I understand is:

I have to define the return-type of the aggregate function => float

The type of thing it aggregates => float (will be the value for each  
row)
An initial value, probably 0

And a state-transition function to accumulate values (takes the  
running total and the next value)

The optionally finalisation function I think I don't need it ...

How I can pack all this info in a aggregate function ?

In the manuals there is this example:

CREATE AGGREGATE avg (
sfunc = float8_accum,
basetype = float8,
stype = float8[],
finalfunc = float8_avg,
initcond = ’{0,0}’
);

but if I use real data with this aggregate, it doesn't work: (note  
that I change avg to test, also in the create aggregate)

GlobalGest=# select test(saldo_deure,saldo_haver,saldo) from comptes  
limit 5;
ERROR:  function test(numeric, numeric, numeric) does not exist at  
character 8
HINT:  No function matches the given name and argument types. You may  
need to add explicit type casts.


thanks in advance,

regards,


raimon fernandez

Responses

pgsql-novice by date

Next:From: Neil SaundersDate: 2007-05-15 21:51:34
Subject: Re: Invalid byte sequence for encoding "UTF8"
Previous:From: Richard Broersma JrDate: 2007-05-15 18:52:03
Subject: Re: PostgreSQL audiobooks and/or podcasts

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group