Re: Computed columns and functions?

Hi all,
I’d like to set up a computed column that uses a function to derive its
data, but can’t seem to find any information on how to do this. My
issue, specifically, is this:

I have a user table and an account activity table, which contains
records of debits and credits to an account, user id as FK. I’d like to
create a computed column within my user table to show the user’s current
balance (sum of credits minus sum of debits). I’ve written a sql query
to compute this when passed in a user id as an argument, but I can’t
figure out how to create the computed column.

Any tips would be much appreciated.

Thanks,
David Benoff

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.515 / Virus Database: 313 - Release Date: 9/1/2003

On Mon, Sep 29, 2003 at 07:49:44 -0700,
David Benoff <dbenoff(at)covad(dot)net> wrote:
> Hi all,
> I’d like to set up a computed column that uses a function to derive its
> data, but can’t seem to find any information on how to do this. My
> issue, specifically, is this:

You can do this with a view.

David,

> I’d like to set up a computed column that uses a function to derive its
> data, but can’t seem to find any information on how to do this. My
> issue, specifically, is this:

The reason you're having trouble finding data is that computed columns are
pretty useless, and there is no built-in mechanism for them in Postgres.

> I have a user table and an account activity table, which contains
> records of debits and credits to an account, user id as FK. I’d like to
> create a computed column within my user table to show the user’s current
> balance (sum of credits minus sum of debits). I’ve written a sql query
> to compute this when passed in a user id as an argument, but I can’t
> figure out how to create the computed column.

If you need this info frequently enough, simply create a view and use that for
accessing your data. Your view will be based on the query you are currently
using.

In some cases, you can find that such a view has poor performance. In that
case, you might want to create a "cache table" that stores the running
totals, and update that via trigger whenever the accounts or users tables are
updated. I'd reccomend *not* storing this data in the users table, lest you
get into an endless loop ....

--
Josh Berkus
Aglio Database Solutions
San Francisco