Re: Watching for view changes

On Sun, 23 Dec 2018 23:06:51 -0800, Mitar <mmitar(at)gmail(dot)com> wrote:

>On Fri, Dec 21, 2018 at 11:10 PM George Neuner <gneuner2(at)comcast(dot)net> wrote:
>> A materialized view IS exactly such a deliberate cache of results from
>> applying a view. It is a real table that can be monitored for changes
>> using INSERT, UPDATE and/or DELETE triggers.
>
>Are you sure one can use triggers on a materialized view? I am getting:
>
>"my_materialized_view" is not a table or view
>
>as an error when I am trying to create a trigger on materialized view.

IIRC the cache table's name is generated. I don't know the proper
incantations to get it from the catalogs, but an easy way to find it
is to put the materialized view into its own tablespace, then search
pg_tables for objects in that space.

George