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

Do you recreate the views every day? Why?

(stupid smartphone-app, sorry for top-posting)

Am 27. Dezember 2015 22:39:58 MEZ, schrieb Wells Oliver <wells.oliver@gmail.com>:
>
>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

--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

Maybe you can create a so called SRF - Funktion (Set Returning Funktion) with proper parameters to use AS 'create materialized view ... AS select * Form your_srf_Funktion(param1,param2,...,paramN). Untested, but i think it should work.


(stupid smartphone-app ...)


--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

On Sun, Dec 27, 2015 at 10:39 PM, Wells Oliver <wells.oliver@gmail.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.

On Sun, 2015-12-27 at 15:39 -0600, Wells Oliver wrote:
> I then have four different views which do largely the same thing,
> totaling the days four different ways:
>
> 1) by last 10
> 2) by the last 30
> 3) by the last 60
> 4) by the entire year
>
> 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
>

Hello, I do not follow why you have four views and presumably run four
separate selects.
Assuming one of the base tables to the view has a column containing a
timestamp, why can't you define the column days_back in the view along
the lines of:-

select ((extract(epoch from current_date) - extract(epoch from
tables_date_column)) / (24 * 60 * 60)) as days_back

Then you can test days_back for the appropriate values using CASE, do
the calculations, and end up running a single select query.

Don't know if this helps. 

Cheers,
Rob