Re: Calculating default values on insert?

On Wed, Nov 05, 2003 at 03:51:36 +0000,
  Robin Munn <rmunn@pobox.com> wrote:
> I'm trying to calculate the default values of a column when I insert a
> row, based on the values of other columns. It's something I thought
> should be simple enough, but I can't seem to figure out how to do it.
> Here's a basic example of what I'm trying to do:

You don't know what order the defaults are going to be done in, so this
approach couldn't work in general. You probably want to do this in a before
trigger anyway in order to maintain integrity between the columns.

>
> CREATE TABLE money (
>   amount numeric NOT NULL,
>   currency text NOT NULL DEFAULT 'USD',
>   currency_per_usd numeric NOT NULL DEFAULT 1.00,
>   usd_amount NOT NULL DEFAULT (amount / currency_per_usd)   -- This fails.
> );
>
> INSERT INTO money (amount) VALUES (50.00);  -- 50 U.S. dollars
>
> INSERT INTO money (amount, currency, currency_per_usd)
> VALUES (
>   50.00,
>   'EUR',
>   1.25
> );         -- 50 Euros at an exchange rate of 1.25 Euros per U.S. dollar
>
> SELECT amount, usd_amount FROM money;
>
>  amount | usd_amount
> --------+------------
>   50.00 |      50.00
>   50.00 |      40.00
>
>
> I realize that I could calculate the usd_amount when I do a SELECT:
>
> SELECT amount, (amount / currency_per_usd) AS usd_amount FROM money;
>
>
> But it would be nice to be able to calculate the default value when I
> INSERT the values.
>
> Is there a way of doing this, or should I just create a VIEW that will
> do the calculation for me?
>
> --
> Robin Munn
> rmunn@pobox.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend