Re: Create trigger on Materialized View?
От | Adrian Klaver |
---|---|
Тема | Re: Create trigger on Materialized View? |
Дата | |
Msg-id | 56FD9681.10909@aklaver.com обсуждение исходный текст |
Ответ на | Re: Create trigger on Materialized View? (Matthew Syphus <MSyphus@lhtac.org>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: