Re: Computed columns and functions?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "David Benoff" <dbenoff(at)covad(dot)net>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Computed columns and functions?
Date: 2003-09-29 17:34:22
Message-ID: 200309291034.22178.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Phil Campaigne 2003-09-29 17:50:25 erro while loading shared libraries: can't open libpq.so.3
Previous Message Bruno Wolff III 2003-09-29 17:19:14 Re: Some Simple Questions