Thread: Adding columns to a view
Hi there, is there any way to add new columns to a view without dropping and recreating it (and thus every other view that depends on it)? A friend of mine came up with a crude hack that involves manipulating the reltype flag in pg_class so Postgres thinks the view is actualy a table, using 'ALTER TABLE' to add a new column, restoring the old reltype and changing the _RETURN rule for that view to include the new column as well. The existence of that "solution" lost me a bet and a crate of beer, but I wouldn't really want to use it in a production-stage database. ;-) 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. Cheers, Ingo
Ingo van Lil wrote: > Hi there, > > is there any way to add new columns to a view without dropping and > recreating it (and thus every other view that depends on it)? A friend > of mine came up with a crude hack that involves manipulating the reltype > flag in pg_class so Postgres thinks the view is actualy a table, using > 'ALTER TABLE' to add a new column, restoring the old reltype and > changing the _RETURN rule for that view to include the new column as > well. The existence of that "solution" lost me a bet and a crate of > beer, but I wouldn't really want to use it in a production-stage > database. ;-) > 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... But of course some other client code could also depend on getting a sorted result-set, but still an order-by clause _can_ be remove. 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.. greetings, Florian Pflug
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).
> 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.
Cheers,
Ingo
>>>>> "Ingo" == Ingo van Lil <inguin@gmx.de> writes: Ingo> Well, in my case the situation is further complicated by the fact that Ingo> adding a column to the view should be done automatically from a trigger Ingo> function. I wanted some kind of matrix view that had a column for every Ingo> row in a certain table. And whenever a new line was inserted into that Ingo> table the view should automatically be extended by one column. This seems wrong, with the same spidey sense tingling that triggered (ugh :) yesterday when I said "sending mail from the database is wrong". Your tables shouldn't change during the execution of your application. If they must, you are probably pushing things that belong "above SQL" (like middleware) and forcing SQL to do them, with all the appropriate trouble that results from that. In general, if it looks difficult to do with PostgreSQL, you're probably heading the wrong direction for good database design. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
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
On 28 Dec 2005, Randal L. Schwartz wrote:
>> 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.
>
> This seems wrong, with the same spidey sense tingling that triggered (ugh :)
> yesterday when I said "sending mail from the database is wrong".
> Your tables shouldn't change during the execution of your application.
You're probably right about extending the view automatically -- I have
to admit it's a pretty sick idea. But the general idea about adding new
columns to that view still seems reasonable to me.
Let me explain my current situation: I'm using Postgres for management
of a student network with some 1500 users and about 2000 hosts. We have
one table that holds information about all the users (primary key
'person_id') and another table that holds a set of possible status flags
that may be assigned to users (primary key 'status_id'). A third table
assigns those status flags to users for a certain interval:
CREATE TABLE user_has_status (
id SERIAL PRIMARY KEY,
person_id integer NOT NULL REFERENCES person(person_id),
status_id integer NOT NULL REFERENCES status(status_id),
valid_from timestamp NOT NULL,
valid_until timestamp NOT NULL
);
Now, if I want to get a list of users that have a certain combination of
valid status entries (e.g. all users that have paid their annual fee and
are not banned for some reason), I have to use several subselects:
SELECT person_id FROM person WHERE
EXISTS (SELECT 1 FROM status WHERE status_id=1
AND CURRENT_TIMESTAMP BETWEEN valid_from AND valid_until)
AND NOT
EXISTS (SELECT 1 FROM status WHERE status_id=2
AND CURRENT_TIMESTAMP BETWEEN valid_from AND valid_until);
This is what I'd like to simplify: My matrix view should contain one
line for each user and one boolean column for each possible status flag.
The field content should be 'true' if the selected user has a currently
valid status entry assigned to it. The above statement could be written
a great deal shorter as:
SELECT person_id FROM person_status_matrix WHERE paid AND NOT banned;
The only problem: From time to time we need to add some new
functionality and need to introduce a new status entry. And in those
cases the matrix view needs to get a new column. That's why I'd like to
extend it without dropping all the views that might depend on it.
Cheers,
Ingo
On Wed, Dec 28, 2005 at 07:29:28PM +0100, Ingo van Lil wrote: > Now, if I want to get a list of users that have a certain combination of > valid status entries (e.g. all users that have paid their annual fee and > are not banned for some reason), I have to use several subselects: > > SELECT person_id FROM person WHERE > EXISTS (SELECT 1 FROM status WHERE status_id=1 > AND CURRENT_TIMESTAMP BETWEEN valid_from AND valid_until) > AND NOT > EXISTS (SELECT 1 FROM status WHERE status_id=2 > AND CURRENT_TIMESTAMP BETWEEN valid_from AND valid_until); > > This is what I'd like to simplify: My matrix view should contain one > line for each user and one boolean column for each possible status flag. > The field content should be 'true' if the selected user has a currently > valid status entry assigned to it. The above statement could be written > a great deal shorter as: > > SELECT person_id FROM person_status_matrix WHERE paid AND NOT banned; Another alternative would be to create a check_status function that did the lookup for you. If done correctly (as in using SQL as the language and setting it to STABLE), the optimizer should inline the fuction, giving you the same performance as the 1st query but without all the typing (btw, isn't that first query missing person_id as part of the WHERE clause in the EXISTS subqueries?) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461