Thread: 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.g.johnston@gmail.com]
> *Sent:* Thursday, March 31, 2016 10:38 AM
> *To:* Matthew Syphus
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] Create trigger on Materialized View?
>
> On Thu, Mar 31, 2016 at 9:30 AM, Matthew Syphus <MSyphus@lhtac.org
> <mailto:MSyphus@lhtac.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.klaver@aklaver.com