> Александр Кайданник wrote: > I need to swap two tables behind view: > And when i am renaming table - its affecting view. > > For example: > > CREATE TABLE verybigtable (id integer primary key, names varchar(10)); > > CREATE TABLE inactive_verybigtable (id integer primary key, names varchar(10)); > > CREATE VIEW showdata AS (SELECT id, names FROM verybigtable); > > > postgres=# \d+ showdata > View "public.showdata" > Column | Type | Modifiers | Storage | Description > --------+-----------------------+-----------+----------+------------- > id | integer | | plain | > names | character varying(10) | | extended | > View definition: > SELECT verybigtable.id, verybigtable.names > FROM verybigtable; > > > > And at moment i need to swipe table behind view. > > ALTER TABLE verybigtable RENAME TO verybigtable_swiping; //giving temporarly name for table > ALTER TABLE inactive_verybigtable RENAME TO verybigtable; //rename inactive_ table to normal > ALTER TABLE verybigtable_swiping RENAME TO inactive_verybigtable; //rename temporarly to active > > > But, view now also changed.And its problem for me. > > postgres=# \d+ showdata > View "public.showdata" > Column | Type | Modifiers | Storage | Description > --------+-----------------------+-----------+----------+------------- > id | integer | | plain | > names | character varying(10) | | extended | > View definition: > SELECT verybigtable.id, verybigtable.names > FROM inactive_verybigtable verybigtable; > > > How to prevent it without recreating view each time ?
What is the problem with recreating the view?
You could use CREATE OR REPLACE VIEW to just change the query. For more complicated view redefinitions, do them inside a transaction, then they will not disturb concurrent sessions.