Re: Problems changing a view

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_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:

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