From: Richard Huxton <dev@archonet.com>
To: betty <liongliong@telkom.net>
To: pgsql-sql@postgresql.org
Subject: Re: conversi ms-sql7 vs postgresql 7.3
Date: Fri, 7 Feb 2003 11:46:38 +0000
Message-ID: <200302071146.38873.dev@archonet.com> (raw)
In-Reply-To: <b1tbo9$2d0t$1@news.hub.org>
References: <b1tbo9$2d0t$1@news.hub.org>
On Thursday 06 Feb 2003 10:10 am, betty wrote:
> Hi..
>
> I have table xx:
> id debet credit balance
> 1 1000 0 0
> 2 2000 0 0
> 3 0 2500 0
> 4 0 100 0
>
> command in ms-sql 7 can use calculate field (column) balance from id=1 to
> id=4:
> "update xx set bal=balance=bal+debet-credit"
> result:
> id debet credit balance
> 1 1000 0 1000
> 2 2000 0 3000
> 3 0 2500 500
> 4 0 100 400
>
> How command sql can use in psotgresql 7.3?
Three options:
1. Write a function to update the records procedurally (see the plpgsql
section of the manuals). I'd probably use a cursor for this.
2. Write a trigger to keep entries up to date as you insert/delete/update
entries (basically, same as above but keeps things up to date)
3. Use a sub-query
richardh=> SELECT * FROM bank;
id | credit | debit | balance
----+--------+-------+---------
1 | 1000 | 0 | 0
2 | 0 | 250 | 0
3 | 2000 | 0 | 0
(3 rows)
richardh=> UPDATE bank SET balance = (SELECT sum(credit)-sum(debit) AS newbal
FROM bank b2 WHERE b2.id<=bank.id);
UPDATE 3
richardh=> SELECT * FROM bank;
id | credit | debit | balance
----+--------+-------+---------
1 | 1000 | 0 | 1000
2 | 0 | 250 | 750
3 | 2000 | 0 | 2750
(3 rows)
Note that for lots of rows, this may be slow. You might want to rewrite it in
the form b2.balance+b2.credit-b2.debit where b2.id=bank.id-1 but this
requires "id" to not have any gaps in the sequence and also needs special
logic to handle id=1 (or whatever the smallest "id" is).
HTH
--
Richard Huxton
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, liongliong@telkom.net
Subject: Re: conversi ms-sql7 vs postgresql 7.3
In-Reply-To: <200302071146.38873.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox