Re: Problems changing a view

On Fri, Dec 2, 2011 at 3:47 AM, Aren Cambre <aren(at)arencambre(dot)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_geom*
> * FROM raw.dallas_rlc_locations*
> * LEFT JOIN raw.dallas_rlc ON dallas_rlc."Loc Code" =
> dallas_rlc_locations.location_code*
> * WHERE dallas_rlc."CitationId" IS NOT NULL*
> * GROUP 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_geom*
> * FROM raw.dallas_rlc_locations*
> * LEFT JOIN raw.dallas_rlc ON dallas_rlc."Loc Code" =
> dallas_rlc_locations.location_code*
> * WHERE dallas_rlc."CitationId" IS NOT NULL*
> * GROUP 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:
> [image: image.png]
>
> 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