Well, I just added to TODO: * Allow VIEW/RULE recompilation when the underlying tables change Is dynamic view a industry-standard name? If so, I will add it to the TODO. Updated TODO is: * Allow VIEW/RULE recompilation when the underlying tables change Another issue is whether underlying table changes should be reflected in the view, e.g. should SELECT * show additional columns if they are added after the view is created. --------------------------------------------------------------------------- Greg Stark wrote: > > Bruce Momjian <pgman@xxxxxxxxxxxxxxxx> writes: > > > How is this different from materialized views, which is already on the > > TODO list? > > It's entirely unrelated. > > Materialized views are about having all the data stored in advance. They're > really tables that have some sort of process to keep the data in them in sync > with other tables according to a view definition. > > These "dynamic views" are really just normal views operationally. The only > difference is what happens to them when DDL changes are made to the objects > they depend on. > > In normal SQL standard views column references are resolved at creation time > and continue to point to the same physical column despite column renames. And > "select *" doesn't change when new columns are added. > > What these users and myself would prefer is something that remembers the > original view definition text and reinterprets it according to the new > definition of the underlying tables. So if I swap two columns by renaming them > I could recompile the view and it would swap which columns were used where. > And if I add new columns "select *" would include the new columns. > > I'm starting to be a little skeptical about "CREATE DYNAMIC VIEW". I think > what would be better to proceed conservatively and just add a "ALTER VIEW > RECOMPILE". That at least gives the user a way to easily recover the original > intention without having to reenter the view definition manually. > > It would also be useful to have a warning when any DDL is done to a column > being used in a view or adding a new column in any table where a view on the > table had a "select *". That would be useful independently of any automagic > recompile feature. Even if the user has to go fetch the original view > definition from his DDL file (which hopefully he saved) the warning will at > least make it more likely he'll remember to do so. > > > IF you find there's support for these ideas from the powers that be then the > TODOs would look something like: > > o Add support for ALTER VIEW RECOMPILE which would recreate a view definition > using the original SQL DDL definition that originally created it. > > o Add warning whenever DDL to a table affects a view dependent on that table. > Such as when a column is altered that is referenced in the view or when a > column is added if a "select *" appears in the view. > > o Add some option to CREATE VIEW to cause the above ALTER VIEW RECOMPILE to > automatically happen whenever DDL to a table affects the view. > > I think the first of these two are no-brainers if they're implemented well. > The third seems less likely to garner immediate support. > > -- > greg > -- Bruce Momjian | http://candle.pha.pa.us pgman@xxxxxxxxxxxxxxxx | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
