Re: [SQL] Calculation dependencies in views

Tom Lane wrote:
>
> I think what you are getting at is avoiding duplicate computations,
> such as the repeated evaluations of x + y in the above example?
>
> My guess is that you are wasting your time to worry about it, unless
> the repeated calculations are *really* expensive (a float add is down
> in the noise ... though a ten-thousand-digit NUMERIC trig function
> might not be).

I was more interested in reducing code complexity but this is good to know
anyway. Thanks a lot for your help. Your last remark brings another question to
mind.

If I do this:

rick=> CREATE TABLE foo (foo numeric(9,2));
CREATE
rick=> CREATE VIEW bar AS SELECT foo * foo AS bar FROM foo;
CREATE
rick=> \d foo
Table = foo
+----------------------------------+--------------------------+-------+
| Field | Type | Length|
+----------------------------------+--------------------------+-------+
| foo | numeric | 9.2 |
+----------------------------------+--------------------------+-------+
rick=> \d bar
View = bar
Query = SELECT ("foo"."foo" * "foo"."foo") AS "bar" FROM "foo";
+----------------------------------+--------------------------+-------+
| Field | Type | Length|
+----------------------------------+--------------------------+-------+
| bar | numeric |65535.65|
+----------------------------------+--------------------------+-------+
^^^^^^^^
What governs the precision of the numeric in the view "bar"? I couldn't find the
specifics of this in the docs. Is it something I need to ever think of? If so,
can I cast it to a smaller numeric? I think 18.4 should be sufficient in this
case.

Thanks again,

--Rick