Re: pgsql: Add CREATE RECURSIVE VIEW syntax

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