All Forums Database
barani_sachin 141 posts Joined 01/12
22 Aug 2013
Refreshing View?

Hi All,
    On top of a table i am creating a view as
replace view v1 as sel * from tabl1;
After creating this view i have added some columns in the base table. I am able to use those columns after replacing the view again. Is this the expected behaviour that if we add some column in the base table we have to refresh the view.
 

dnoeth 4628 posts Joined 11/04
22 Aug 2013

This is Standard SQL behaviour.
The columns returned by a view are resolved when the view is created.
A SELECT * represents the existing columns at that point in time.
 
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
22 Aug 2013

Thanks Dieter :-)

ToddAWalter 316 posts Joined 10/11
22 Aug 2013

It's a feature! If you do not replace the view, the applications using it are unaffected by the changes to the underlying table definitions.

barani_sachin 141 posts Joined 01/12
22 Aug 2013

Thanks Todd for your time :-)
Why i had this confusion is, that the view will be materialised only during the actual usage right? So my understanding was the exact query which i used to create a view will be ran during the actual usage of the view.
But after Dieter's explanation my understanding is eventhough the view will be materialised during the actual usage but the columns would have been resolved when i created the view and it will be as such unless i replace it with the new one.

dnoeth 4628 posts Joined 11/04
23 Aug 2013

Do a SHOW QUALIFIED SELECT * FROM view and you'll see what source code actually stored.
 
Dieter

Dieter

You must sign in to leave a comment.