Re: Options for complex materialized views sharing most of the same logic?

On Sun, Dec 27, 2015 at 10:39 PM, Wells Oliver <wells(dot)oliver(at)gmail(dot)com> wrote:
> I have one view which totals about 60 columns per day. Each day has a "days
> ago" column like so:
.....
> Each of these views is basically a copy of one another for 99% of the code
> (the summing, percentages, etc). The only differences are:
>
> 1) checks the days_back <= 10
> 2) checks days_back <= 30
> 3) checks days_back <= 60
> 4) does not check days_back
>
> Is there some easier way for me to maintain the structure of the view
> without copying/pasting it 4 times and making one small tweak? I find myself
> adding/removing columns to these views and I do it 4 times each time.

Without knowing more details I would sugest making number 4 and then
defining 1-3 as select * from v4 wher days_back<=xxxx.

And, for the copy/paste stuff, you can do it easily with an script. I
normally would do it in some scripting language but you can even do it
in psql with some creative variable usage, just put the head stuf in a
var, the tail in another one and compose the sentences using them,
simplified example:

s=> \set head 'select version() where 2>1'
s=> \set tail 'and 3>1'
s=> :head :tail;
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.10 on x86_64-pc-linux-gnu, compiled by
x86_64-pc-linux-gnu-gcc (Gentoo 4.9.3 p1.4, pie-0.6.4) 4.9.3, 64-bit
(1 row)

s=> :head and 0>1 :tail ;
version
---------
(0 rows)

If posible I would try the composite stuff mentioned first, but one of
these should be enough, in the second case you still recreate the
things, but let the macro processor do the boring stuff.

Francisco Olarte.