Re: View not allowing to drop column (Bug or Feature enhancement )

Hi,

>
> *
>> *
>> *While trying to drop a column by replacing view definition from view it
>> throws an error saying cannot drop column from view.*
>> *=====================================================================*
>> postgres=# create or replace view vi1 as select
>> id , name from orgdata ;
>> *ERROR: cannot drop columns from view*
>>
>
> You need to drop the view before recreating it. Then it works. If you
> changed the access to the view with grants or revokes, you also neet to
> recreate them. They are dropped with the view.
>
>
Sorry to say but If we need to drop and replace then what is use of "Create
OR Replace " syntax?

> If its not a bug and a limitation kindly guide me towards any
>> documentation where it is mentioned.
>>
>
> http://www.postgresql.org/docs/current/static/sql-createview.html
>
> CREATE OR REPLACE VIEW is similar, but if a view of the same name already
> exists, it is replaced. The new query must generate the same columns that
> were generated by the existing view query (that is, the same column names
> in the same order and with the same data types), but it may add additional
> columns to the end of the list. The calculations giving rise to the output
> columns may be completely different.
>
>
If this is the limitation. Is community is planning update this or add this
feature soon?

On Mon, May 16, 2016 at 12:50 PM, Francisco Olarte <folarte(at)peoplecall(dot)com>
wrote:

> On Mon, May 16, 2016 at 8:49 AM, Shrikant Bhende
> <shrikantbhende(dot)net(at)gmail(dot)com> wrote:
> > While working on the view I came across an unusual behaviour of the view,
> > PostgreSQL do not allows to drop a column from the view, whereas same
> > pattern of Create and Replace view works while adding a column.
>
> This is probably because you are using create or replace, which is
> normally used to switch things in place and so it needs them to be
> compatible with the old ones. A view with an extra column can be used
> instead of the old one, but a view with less columns can not. I do not
> see the 'not dropping' part as unusual, and the 'can add columns',
> well, I see them as a little unusual on a create or replace but I see
> the point in hallowing it, so just a little.
>
> > Alter command do not have any option to drop column
> > postgres=# alter view vi1
> > ALTER COLUMN OWNER TO RENAME TO SET SCHEMA
>
> Well, it is a view, not a table. They are basically shorthands for
> queries and places to attach triggers, so its normal they do not have
> as much management options.
>
> > If its not a bug and a limitation kindly guide me towards any
> documentation
> > where it is mentioned.
>
> Right at the top of create view? :
>
> >>>>
> Description
>
> CREATE VIEW defines a view of a query. The view is not physically
> materialized. Instead, the query is run every time the view is
> referenced in a query.
>
> CREATE OR REPLACE VIEW is similar, but if a view of the same name
> already exists, it is replaced. The new query must generate the same
> columns that were generated by the existing view query (that is, the
> same column names in the same order and with the same data types), but
> it may add additional columns to the end of the list. The calculations
> giving rise to the output columns may be completely different.
> <<<<<<
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--

Thanks and Regards,
Sachin Kotwal