Re: [GENERAL] Adding columns to a view
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Adding columns to a view
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Adding columns to a view
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 ", 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 ( smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Adding columns to a view
> "Ingo" == Ingo van Lil <[EMAIL PROTECTED]> 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 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! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Adding columns to a view
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 ", 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Adding columns to a view
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 ", 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster