pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconversi ms-sql7 vs postgresql 7.3
6+ messages / 5 participants
[nested] [flat]
* conversi ms-sql7 vs postgresql 7.3
@ 2003-02-06 10:10 betty <liongliong@telkom.net>
2003-02-07 11:46 ` Re: conversi ms-sql7 vs postgresql 7.3 Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: betty @ 2003-02-06 10:10 UTC (permalink / raw)
To: pgsql-sql
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?
thank's a lot
betty.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: conversi ms-sql7 vs postgresql 7.3
2003-02-06 10:10 conversi ms-sql7 vs postgresql 7.3 betty <liongliong@telkom.net>
@ 2003-02-07 11:46 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2003-02-07 11:46 UTC (permalink / raw)
To: betty <liongliong@telkom.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: conversi ms-sql7 vs postgresql 7.3
@ 2003-02-07 09:59 Christoph Haller <ch@rodos.fzk.de>
2003-02-10 05:10 ` Re: conversi ms-sql7 vs postgresql 7.3 betty <liongliong@telkom.net>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Haller @ 2003-02-07 09:59 UTC (permalink / raw)
To: pgsql-sql; +Cc: liongliong@telkom.net
>
> 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?
>
Try
UPDATE xx SET balance=balance+debet-credit ;
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: conversi ms-sql7 vs postgresql 7.3
2003-02-07 09:59 Re: conversi ms-sql7 vs postgresql 7.3 Christoph Haller <ch@rodos.fzk.de>
@ 2003-02-10 05:10 ` betty <liongliong@telkom.net>
2003-02-10 21:04 ` Re: conversi ms-sql7 vs postgresql 7.3 greg@turnstep.com
0 siblings, 1 reply; 6+ messages in thread
From: betty @ 2003-02-10 05:10 UTC (permalink / raw)
To: pgsql-sql
hi Christoph,
your command :
"UPDATE xx SET balance=balance+debet-credit "
result calculate balance for each row, ex:
id debet credit balance
1 1000 0 1000
2 2000 0 2000
3 0 2500 -2500
4 0 100 -100
not result calculate balance, ex:
id debet credit balance
1 1000 0 1000
2 2000 0 3000
3 0 2500 500
4 0 100 400
regards
betty
"Christoph Haller" <ch@rodos.fzk.de> wrote in message
news:3E438396.2983BD2B@rodos.fzk.de...
> >
> > 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?
> >
> Try
> UPDATE xx SET balance=balance+debet-credit ;
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: conversi ms-sql7 vs postgresql 7.3
2003-02-07 09:59 Re: conversi ms-sql7 vs postgresql 7.3 Christoph Haller <ch@rodos.fzk.de>
2003-02-10 05:10 ` Re: conversi ms-sql7 vs postgresql 7.3 betty <liongliong@telkom.net>
@ 2003-02-10 21:04 ` greg@turnstep.com
2003-02-10 21:40 ` Re: conversi ms-sql7 vs postgresql 7.3 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: greg@turnstep.com @ 2003-02-10 21:04 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> 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"
You cannot do such a thing in SQL alone: you must use a procedural
language. One way is with plpgsql:
CREATE OR REPLACE FUNCTION makebalance() RETURNS VARCHAR LANGUAGE 'plpgsql' AS
'
DECLARE
total INTEGER := 0;
myrow RECORD;
BEGIN
FOR myrow IN SELECT id, credit-debit AS cd FROM xx ORDER BY id ASC LOOP
total := total + myrow.cd;
UPDATE xx SET balance = total WHERE id=myrow.id;
END LOOP;
RETURN ''Final balance: '' || total;
END;
';
This is just a rough idea. You may have to create the language first:
see the "createlang" script in the bin directory of your PostgreSQL installation.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302101601
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+SBN9vJuQZxSWSsgRAoNEAJ9IKmRW6IlHu12x2w7i0G6THB7oGACgi7lM
bIyssS1GQRgY0aQFRzyQKl8=
=ArPb
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: conversi ms-sql7 vs postgresql 7.3
2003-02-07 09:59 Re: conversi ms-sql7 vs postgresql 7.3 Christoph Haller <ch@rodos.fzk.de>
2003-02-10 05:10 ` Re: conversi ms-sql7 vs postgresql 7.3 betty <liongliong@telkom.net>
2003-02-10 21:04 ` Re: conversi ms-sql7 vs postgresql 7.3 greg@turnstep.com
@ 2003-02-10 21:40 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-02-10 21:40 UTC (permalink / raw)
To: greg@turnstep.com; pgsql-sql
Greg,
> > 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"
>
> You cannot do such a thing in SQL alone: you must use a procedural
> language. One way is with plpgsql:
You're mistaken, I think (I can't find the original e-mail in this thread)
UPDATE totals SET balance = total_bal
FROM (SELECT acct_id, (sum(credit) - sum(debit)) as total_bal
FROM accounts GROUP BY acct_id) tb
WHERE tb.acct_id = totals.acct_id
AND totals.acct_id = $selection
... would update a ficticious "totals" table with the sum of credits and
debits for a particular account.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-02-10 21:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-06 10:10 conversi ms-sql7 vs postgresql 7.3 betty <liongliong@telkom.net>
2003-02-07 11:46 ` Richard Huxton <dev@archonet.com>
2003-02-07 09:59 Re: conversi ms-sql7 vs postgresql 7.3 Christoph Haller <ch@rodos.fzk.de>
2003-02-10 05:10 ` betty <liongliong@telkom.net>
2003-02-10 21:04 ` greg@turnstep.com
2003-02-10 21:40 ` Josh Berkus <josh@agliodbs.com>
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