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

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Options for complex materialized views sharing most of the same logic?
Date: 2015-12-27 21:39:58
Message-ID: CAOC+FBX68vP2bZw8qc=Y1KYk1gUUM=Xu43BzEqsdsWdrb=7U3g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi guys, hope everyone's well. I'm in a situation that we find ourselves in
a lot, and I'm wondering if there's an easier option.

I have one view which totals about 60 columns per day. Each day has a "days
ago" column like so:

row_number() over (order by date desc) as days_back

Reason being is that there's not an entry every day, so it's useful. 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

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.

Thanks!

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2015-12-27 22:03:03 Re: Options for complex materialized views sharing most of the same logic?
Previous Message Adrian Klaver 2015-12-27 20:36:40 Re: Calling function (table_name, schema_name) within event trigger