Re: Computed columns and functions?

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