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@xxxxxxxxx]
*Sent:* Thursday, March 31, 2016 10:38 AM
*To:* Matthew Syphus
*Cc:* pgsql-general@xxxxxxxxxxxxxx
*Subject:* Re: Create trigger on Materialized View?
On Thu, Mar 31, 2016 at 9:30 AM, Matthew Syphus <MSyphus@xxxxxxxxx
<mailto:MSyphus@xxxxxxxxx>> 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
