Calculating default values on insert?

[Robin Munn]

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:

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
rm***@pobox.com

[Bruno Wolff III]

On Wed, Nov 05, 2003 at 03:51:36 +0000,
Robin Munn <rm***@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
rm***@pobox.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org