Re: [GENERAL] Create trigger on Materialized View?

[Matthew Syphus]

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?

From: David G. Johnston [mailto:david.g.johns...@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 
mailto:msyp...@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.

[GENERAL] Create trigger on Materialized View?

[Matthew Syphus]

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

Thank you,
MS