Skip site navigation (1) Skip section navigation (2)

Re: Adding columns to a view

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Ingo van Lil <inguin(at)gmx(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Adding columns to a view
Date: 2005-12-29 10:48:49
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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

greetings, Florian Pflug

In response to

pgsql-general by date

Next:From: Simon RiggsDate: 2005-12-29 11:58:06
Subject: Re: WAL logs multiplexing?
Previous:From: Zlatko MatićDate: 2005-12-29 10:31:33
Subject: POstgreSQL 8.1.X/Lazarus?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group