Обсуждение: pgsql: Add CREATE RECURSIVE VIEW syntax
Add CREATE RECURSIVE VIEW syntax This is specified in the SQL standard. The CREATE RECURSIVE VIEW specification is transformed into a normal CREATE VIEW statement with a WITH RECURSIVE clause. reviewed by Abhijit Menon-Sen and Stephen Frost Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/583905269378bf41c24585773885b1e226a998ce Modified Files -------------- doc/src/sgml/ref/create_view.sgml | 29 ++++++++++++- src/backend/parser/gram.y | 85 ++++++++++++++++++++++++++++++++++++ src/test/regress/expected/with.out | 30 +++++++++++++ src/test/regress/sql/with.sql | 15 ++++++ 4 files changed, 158 insertions(+), 1 deletions(-)
On 1 February 2013 03:33, Peter Eisentraut <peter_e@gmx.net> wrote: > Add CREATE RECURSIVE VIEW syntax > > This is specified in the SQL standard. The CREATE RECURSIVE VIEW > specification is transformed into a normal CREATE VIEW statement with a > WITH RECURSIVE clause. Recursive views aren't updatable, but the error message may be considered a bit confusing to those who don't realise that recursive views are converted to recursive CTEs. postgres=# UPDATE vpeople SET last_person = last_person || ' test'; ERROR: cannot update view "vpeople" DETAIL: Views containing WITH are not automatically updatable. HINT: To make the view updatable, provide an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger. Could we perhaps modify the error message in such scenarios to something like: "Views containing WITH, including recursive views, are not automatically updatable." -- Thom
On Thu, 2013-02-28 at 14:38 +0000, Thom Brown wrote: > Recursive views aren't updatable, but the error message may be > considered a bit confusing to those who don't realise that recursive > views are converted to recursive CTEs. > > postgres=# UPDATE vpeople SET last_person = last_person || ' test'; > ERROR: cannot update view "vpeople" > DETAIL: Views containing WITH are not automatically updatable. > HINT: To make the view updatable, provide an unconditional ON UPDATE > DO INSTEAD rule or an INSTEAD OF UPDATE trigger. > > Could we perhaps modify the error message in such scenarios to > something like: "Views containing WITH, including recursive views, are > not automatically updatable." If a user saw that error message and did a \d on the view, it would show the expanded form with WITH, so I think the message is fine as it is.