From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Ingo van Lil <ingo(at)vanlil(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Adding columns to a view |
Date: | 2005-12-28 02:05:56 |
Message-ID: | 43B1F304.2050504@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Aftab Alam | 2005-12-28 03:25:16 | sending mail from Postgres |
Previous Message | Dann Corbit | 2005-12-28 01:06:46 | Re: Detaching database |