Updatable view columns
От | Dean Rasheed |
---|---|
Тема | Updatable view columns |
Дата | |
Msg-id | CAEZATCULXejsZVq3PvG8RJReyXwdPoZU_Myr6H2DWW5=ioR=NA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Updatable view columns
|
Список | pgsql-hackers |
Hi, Attached is a work-in-progress patch to extend auto-updatable views to support views containing a mix of updatable and non-updatable columns. This is basically the "columns" part of SQL Feature T111, "Updatable joins, unions, and columns". So specifically, views of the following form will now be auto-updatable: SELECT <column, expression, sub-query or non-SRF>, ... FROM <single base table or view> WHERE <arbitrary quals> [ORDER BY ...] with the restriction that no window functions, aggregates or set-returning functions may appear in the view's targetlist (because otherwise the rewriting process may move them up into quals of the top-level query where they are not allowed). Hopefully this will make auto-updatable views much more useful, since it covers a much wider class of real-world views. INSERT and UPDATE are supported provided that they do not attempt to assign to a non-updatable column (which currently means a column that is not a simple reference to an updatable column of the base relation). This also means that the view must have at least 1 updatable column for these operations, which is per-spec. DELETE on the other hand doesn't actually require any updatable columns, provided the view satisfies all the other updatability requirements (single base relation, no distinction, grouping, etc...). This is actually an extension of the spec, which says that DELETE should only be supported on updatable views with at least 1 updatable column, but having played around with the code, it seems it would be an annoying amount of additional code to enforce this restriction, so I don't see any reason to not just allow it. Code-wise, aside from the obvious changes needed to the xxx_is_updatable() functions, the only other code change needed to support this is in rewriteTargetListIU(). This had code for UPDATE which would add new dummy targetlist entries for unassigned-to attributes in the view, similar to expand_targetlist() in preptlist.c. It now only does this for trigger-updatable views. For auto-updatable views, it is definitely the wrong thing to do, since it would assign targetlist entries for all the non-updatable columns. For rule-updatable views it was not wrong, but I think it was unnecessary, since the rule will rewrite the query with a different target relation, which would then get recursively processed. Basically, adding these dummy targetlist entries is only necessary if the view relation remains the target after rewriting. That's consistent with the fact that this code-block was only added in 9.1 to support triggers on views. It still needs more testing and doc updates, but otherwise I hope it will be in reasonable shape for the next commitfest. Regards, Dean
Вложения
В списке pgsql-hackers по дате отправления: