Re: Problems changing a view

Could pgAdmin detect if the view has no dependents and offer to drop and
replace the view?

My views generally have no dependents. Don't know if that's typical,
however.

Aren

On Fri, Dec 2, 2011 at 3:08 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

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