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