Re: Create trigger on Materialized View?

On 03/31/2016 09:46 AM, Matthew Syphus wrote:
> I was afraid of that. Any idea if it is a technical or resource
> limitation? In other words, is such functionality impossible,
> undesirable, anticipated, or in the works?

I would guess from here:
http://www.postgresql.org/docs/9.5/static/rules-materializedviews.html

"Materialized views in PostgreSQL use the rule system like views do, but
persist the results in a table-like form. The main differences between:

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

and:

CREATE TABLE mymatview AS SELECT * FROM mytab;

are that the materialized view cannot subsequently be directly updated
..."

>
> *From:*David G. Johnston [mailto:david(dot)g(dot)johnston(at)gmail(dot)com]
> *Sent:* Thursday, March 31, 2016 10:38 AM
> *To:* Matthew Syphus
> *Cc:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] Create trigger on Materialized View?
>
> On Thu, Mar 31, 2016 at 9:30 AM, Matthew Syphus <MSyphus(at)lhtac(dot)org
> <mailto:MSyphus(at)lhtac(dot)org>> wrote:
>
> I would like a trigger on a materialized view. Is this possible? I
> have tried both an INSTEAD OF and BEFORE trigger with no luck. It
> responds with:
>
> "project_milestone_mv" is not a table or view.
>
> It is absolutely present and spelled correctly. It is the same with
> or without the schema qualification. Actual statement:
>
> CREATE TRIGGER project_milestone_upsert_trigger INSTEAD OF UPDATE
>
> ON tracking.project_milestone_mv FOR EACH ROW
>
> EXECUTE PROCEDURE tracking.project_milestone_upsert();
>
> The documentation at
> http://www.postgresql.org/docs/current/static/sql-createtrigger.html
> does not explicitly exclude nor include materialized views. The
> most pertinent part I’ve found simply states “The trigger will be
> associated with the specified table, view, or foreign table” and
> later in reference to table_name, “The name (optionally
> schema-qualified) of the table, view, or foreign table the trigger
> is for.” I’ve found no mailing list entry addressing triggers and
> whether “view” does not include _/materialized/_ view.
>
> Does this indicate, then, that materialized views cannot have triggers?
>
> Postgres 9.5.1
>
> CentOS 6.5
>
> ​That would seem to be sufficient evidence that indeed "materialized
> views" are not the same as "views" (or "tables") and that they have not
> been given the ability to be assigned triggers.
>
> While still implicit the documentation page for "SQL Commands" has
> separate entries for "CREATE VIEW" and "CREATE MATERIALIZED VIEW" which
> further supports them being distinct as opposed to materialized views
> being a specialization of view.
>
> David J.
>
> ------------------------------------------------------------------------
>
> This email has been scanned for spam and viruses by Proofpoint
> Essentials cloud email security - click here
> <https://us1.proofpointessentials.com/index01.php?mod_id=11&mod_option=logitem&mail_id=p2DG0UJ7Kyjv&rid=6780640&report=1>
> to report this email as spam.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com