Re: return modified data from a function

Raimon Fernandez ha escrito:
> Hi,
>
> We have a table for an account system:
>
> table:
> compte
>
>
> fields:
> detail => varchar
> deure => float
> haver => float
>
>
> some data:
>
> detail: Entrada
> deure: 0
> haver: 5000
>
> detail: Sortida
> deure: 100
> haver: 0
>
> detail: Comptes
> deure: 150
> haver: 0
>
> detail: Varis
> deure: 35
> haver: 0
>
>
> We want to retrieve all this records, with an extra column with the
> difference from deure-haver, like this:
>
>
> Entrada 0 5000 5000
> Sortida 100 0 4900
> Comptes 150 0 4750
> Varis 35 0 4715
>
> We tried using functions, with a cursor, and add an extra column or
> tried to create a view, but without success ...
>
> We have a workaround using our frontend, and there we calculate the
> difference, but we want to do it from PostgreSQL.
>
> It's easy to do it ?
>
>
> thanks in advance,
>
> regards,
>
>
> raimon fernandez
> barcelona

I believe you need some kind of sequence for the transactions, to
control ordering (and maybe some kind of timestamp, but let's just use
the sequence for this example):

CREATE TABLE compte
(
detail VARCHAR,
deure FLOAT,
haver FLOAT,
seq serial PRIMARY KEY
);

INSERT INTO compte VALUES('Entrada', 0, 5000);
INSERT INTO compte VALUES('Sortida', 100, 0);
INSERT INTO compte VALUES('Comptes', 150, 0);
INSERT INTO compte VALUES('Varis', 35, 0);

SELECT a.*
, COALESCE((SELECT haver - deure
FROM compte
WHERE seq = 1)
+ (SELECT SUM(haver - deure)
FROM compte
WHERE seq >1
AND seq <= a.seq), 0) AS difference
FROM compte a