Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group