Re: Calculating default values on insert?

On Wed, Nov 05, 2003 at 03:51:36 +0000,
Robin Munn <rmunn(at)pobox(dot)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(at)pobox(dot)com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend