Ingo van Lil wrote:
On 28 Dec 2005, Florian G. Pflug wrote:I could think of a few situations where extending a view might be useful, and I'd appreciate to see it supported. I don't see any reason not to allow it as long as no existing columns are removed or have their type changed.Well, some other view could do "select * from <firstview>", or some client code could assume a certain number of rows, and missbehave if there are more rows...Other views wouldn't see the newly added column, a 'select * from' is automatically rewritten as 'select column1, column2, ... from' when creating views. As for misbehaving client code: That's the client's problem, not the database's. From a client's point of view there's no difference between adding a new field to a table (which is allowed) and adding a new field to a view (which isn't).
Good point.
If I need to change the order or number of columns in a view, I use pgadmin to find the dependent objects, copy their definitions into a sql-window (including the "drop ... " line), put my new definition and a "drop cascade " in front, and execute all that inside a transaction. But you're right, if more then 5 or so other objects depend on a view, this gets pretty annyoing..Well, in my case the situation is further complicated by the fact that adding a column to the view should be done automatically from a trigger function. I wanted some kind of matrix view that had a column for every row in a certain table. And whenever a new line was inserted into that table the view should automatically be extended by one column. Well, fortunately adding a new entry to that table happens only very rarely, and I wouldn't mind extending the view manually. I'd just prefer to be able to do so without dropping and recreating everything that depends on it.
Hm... if I remember correctly, your "hack" was to add the column manually by altering the system catalogs, and then modifying the on-select rule. If you created the view "manually", meaning that instead of "create view ..." you do "create table (<fields)" and then add in on-select rule, you could add a column to the view without messing around in the system catalogs. You'd just do "alter table add column", and then update the on-select rule accordingly. greetings, Florian Pflug
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
