Content-Type: text/plain;
  charset="utf-8"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "betty" <liongliong@telkom.net>, pgsql-sql@postgresql.org
Subject: Re: conversi ms-sql7 vs postgresql 7.3
Date: Fri, 7 Feb 2003 11:46:38 +0000
User-Agent: KMail/1.4.3
References: <b1tbo9$2d0t$1@news.hub.org>
In-Reply-To: <b1tbo9$2d0t$1@news.hub.org>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302071146.38873.dev@archonet.com>

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=3D1 =
to
> id=3D4:
> "update xx set bal=3Dbalance=3Dbal+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=20
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=
=20
entries (basically, same as above but keeps things up to date)

3. Use a sub-query
richardh=3D> SELECT * FROM bank;
 id | credit | debit | balance
----+--------+-------+---------
  1 |   1000 |     0 |       0
  2 |      0 |   250 |       0
  3 |   2000 |     0 |       0
(3 rows)

richardh=3D> UPDATE bank SET balance =3D (SELECT sum(credit)-sum(debit) AS =
newbal=20
FROM bank b2 WHERE b2.id<=3Dbank.id);
UPDATE 3
richardh=3D> 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=20
the form b2.balance+b2.credit-b2.debit where b2.id=3Dbank.id-1 but this=20
requires "id" to not have any gaps in the sequence and also needs special=
=20
logic to handle id=3D1 (or whatever the smallest "id" is).

HTH

--=20
  Richard Huxton