Re: [SQL] Calculation dependencies in views

Jan Wieck wrote:
>
> Rick Delaney wrote:
>
> >
> Be aware that 6.5.* might be unable to deal with the resulting size of
> the rewrite rule. And I'm not sure that I'll get TOAST ready for 7.0 to
> handle it.
>
[snip]
> But if you're able to do the nesting in a manner like this:
>
> CREATE VIEW one AS
> SELECT
> x, y, z,
> x + y AS sum_xy,
> x + z AS sum_xz,
> y + z AS sum_yz
> FROM my_table;
>
> CREATE VIEW final AS
> SELECT
> x, y, z,
> sum_xy AS a,
> sum_yz AS b,
> sum_xy * z AS c,
> sum_yz * x AS d,
> sum_xy * z + x AS e,
> sum_yz * x + x AS f
> FROM one;
>
> and keep all views follow strictly one path
>
> final -> n-1 -> n-2 -> ... -> two -> one
>
> you'll end up the a construct that requires nested levels of rewriting.
> But the resulting query will definitely be a single table scan having
> all your complicated expressions attached.

I'd have to take a good look at all the expressions again to know if I could do
this but I think I probably can. Only, with what I've started on I'm already
getting "rule plan string too big" errors. (What I've started on is just one
view that doesn't have all the expressions in it yet).

So I will just get the same thing with nested views, no? That is how I
understand the "The Postgres Rule System" section of the manual and Tom Lane's
answer to my post.

If I could structure things as

subset_1_n -> subset_1_n-1 -> ... -> subset_1_2 -> subset_1_1
subset_2_n -> subset_2_n-1 -> ... -> subset_2_2 -> subset_2_1
:
:
subset_m_n -> subset_m_n-1 -> ... -> subset_m_2 -> subset_m_1

final -> join subset_1_n, subset_2_n, ..., subset_m_n

where each view subset_i_n has a usably-sized rule plan string then could I get
this thing working?

I can (and will) try this out with my stuff but I'm just trying to understand the
general principles.

> But if you're dealing with huge set's of row's at SELECT time and rarely
> changes, you might be better off with setting up a table having all the
> fields in place, and let a trigger put all the calculated values in
> place.

This will be primarily INSERT/UPDATE a row and then SELECT the view of that row.
Am I right that a trigger would make more sense in this case then?

Thanks a lot for your help,

--Rick