Re: Problems changing a view
От | Dave Page |
---|---|
Тема | Re: Problems changing a view |
Дата | |
Msg-id | CA+OCxoyQ02nMt-6JdFN_wcLJzbUcHOC6S0pkfwTCoq-VT8uFFQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Problems changing a view (Aren Cambre <aren@arencambre.com>) |
Ответы |
Re: Problems changing a view
(Aren Cambre <aren@arencambre.com>)
|
Список | pgadmin-support |
On Fri, Dec 2, 2011 at 3:47 AM, Aren Cambre <aren@arencambre.com> wrote:
If I change this view:SELECT dallas_rlc_locations.location_code, count(dallas_rlc."CitationId") AS count, dallas_rlc_locations.location, dallas_rlc_locations.the_geomFROM raw.dallas_rlc_locationsLEFT JOIN raw.dallas_rlc ON dallas_rlc."Loc Code" = dallas_rlc_locations.location_codeWHERE dallas_rlc."CitationId" IS NOT NULLGROUP BY dallas_rlc_locations.location, dallas_rlc_locations.location_code, dallas_rlc_locations.the_geom;...to this:SELECT count(dallas_rlc."CitationId") AS count, dallas_rlc_locations.location_code, dallas_rlc_locations.location, dallas_rlc_locations.the_geomFROM raw.dallas_rlc_locationsLEFT JOIN raw.dallas_rlc ON dallas_rlc."Loc Code" = dallas_rlc_locations.location_codeWHERE dallas_rlc."CitationId" IS NOT NULLGROUP BY dallas_rlc_locations.location, dallas_rlc_locations.location_code, dallas_rlc_locations.the_geom;(I just reversed the first two fields in the SELECT)I get this error:Huh? A few is impermanent. There's no reason why I can't arbitrarily change the view's underlying code as I wish, as long as the final result is valid SQL. Even if PostgreSQL has some kind of limitation (not alleging it does, just speculating), couldn't pgAdmin help us out here and get us around the limitation?
The problem is that once a view is created it can only be replaced with another one which will produce the same set of columns, taking into account names and datatypes. You can work around that by dropping it first, but, if there are dependent objects then you'll need to drop and recreate them first of course - which is exactly why you're prevented from changing column names/types in the first place; so you don't break dependent objects.
So, you can drop all dependents, then drop and recreate the view and then recreate the dependents, or you can modify the view in place, but without changing column names or types.
This is a restriction of PostgreSQL - pgAdmin doesn't have any ability to parse the SQL in the views, so has no idea what you're actually changing and whether or not it would be allowed. Adding that capability would be a bucketload of work, for little gain.
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
В списке pgadmin-support по дате отправления: