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 <fola...@peoplecall.com> wrote: > On Mon, May 16, 2016 at 8:49 AM, Shrikant Bhende > <shrikantbhende....@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Thanks and Regards, Sachin Kotwal
