Postgresql – a good way to parameterize a materialized view on Postgres

materialized-viewpostgresql

Given for example a materialized view like this (Postgres 10.3):

create materialized view my_view as
    select * from my_table where sell_date < '2018-03-01';

The sell_date comparison value ('2018-03-01') can change sometime, but I want to avoid to drop and recreate the materialized view each time. The only idea I came up with it is using an external table with some metadata values like the date needed:

create materialized view my_view as
    select * from my_table where sell_date < 
        (select original_sell_date from some_metadata_table);

Is there any other way to work around this limitation of materialized views on Postgres?

One of the problems with my current solution is that you can have two or more materialized views that use the same value, but they may need at some point of different values. In this case a duplication of the metadata table is needed.

Best Answer

There are three ways,

  • Use foreign data in a table that holds the inputs.
  • Make sell_date a function of the current time. Such as AND sell_date > current_time - '5 days'.
  • If nothing depends on it, drop and reload a new ad-hoc MATERIALIZED VIEW by the same name in a transaction.
Related Question