On Thu, Oct 31, 2019 at 5:11 PM Ibrar Ahmed <ibrar.ah...@gmail.com> wrote:
> > > On Thu, Oct 31, 2019 at 5:01 PM Fujii Masao <masao.fu...@gmail.com> wrote: > >> On Thu, Oct 31, 2019 at 7:59 PM Ibrar Ahmed <ibrar.ah...@gmail.com> >> wrote: >> > >> > >> > >> > On Thu, Oct 31, 2019 at 12:32 PM Fujii Masao <masao.fu...@gmail.com> >> wrote: >> >> >> >> On Thu, Oct 31, 2019 at 1:42 PM Tom Lane <t...@sss.pgh.pa.us> wrote: >> >> > >> >> > Fujii Masao <masao.fu...@gmail.com> writes: >> >> > > Currently CREATE OR REPLACE VIEW command fails if the column names >> >> > > are changed. >> >> > >> >> > That is, I believe, intentional. It's an effective aid to catching >> >> > mistakes in view redefinitions, such as misaligning the new set of >> >> > columns relative to the old. That's particularly important given >> >> > that we allow you to add columns during CREATE OR REPLACE VIEW. >> >> > Consider the oversimplified case where you start with >> >> > >> >> > CREATE VIEW v AS SELECT 1 AS x, 2 AS y; >> >> > >> >> > and you want to add a column z, and you get sloppy and write >> >> > >> >> > CREATE OR REPLACE VIEW v AS SELECT 1 AS x, 3 AS z, 2 AS y; >> >> > >> >> > If we did not throw an error on this, references that formerly >> >> > pointed to column y would now point to z (as that's still attnum 2), >> >> > which is highly unlikely to be what you wanted. >> >> >> >> This example makes me wonder if the addtion of column by >> >> CREATE OR REPLACE VIEW also has the same (or even worse) issue. >> >> That is, it may increase the oppotunity for users' mistake. >> >> I'm thinking the case where users mistakenly added new column >> >> into the view when replacing the view definition. This mistake can >> >> happen because CREATE OR REPLACE VIEW allows new column to >> >> be added. But what's the worse is that, currently there is no way to >> >> drop the column from the view, except recreation of the view. >> >> Neither CREATE OR REPLACE VIEW nor ALTER TABLE support >> >> the drop of the column from the view. So, to fix the mistake, >> >> users would need to drop the view itself and recreate it. If there are >> >> some objects depending the view, they also might need to be recreated. >> >> This looks not good. Since the feature has been supported, >> >> it's too late to say that, though... >> >> >> >> At least, the support for ALTER VIEW DROP COLUMN might be >> >> necessary to alleviate that situation. >> >> >> > >> > - Is this intentional not implemented the "RENAME COLUMN" statement for >> > VIEW because it is implemented for Materialized View? >> >> Not sure that, but Tom's suggestion to support ALTER VIEW RENAME COLUMN >> sounds reasonable whether we support the rename of columns when replacing >> the view definition, or not. Attached is the patch that adds support for >> ALTER VIEW RENAME COLUMN command. >> >> > I have made just a similar >> > change to view and it works. >> >> Yeah, ISTM that we made the same patch at the same time. You changed >> gram.y, >> but I added the following changes additionally. >> >> - Update the doc >> - Add HINT message emit when CRAETE OR REPLACE VIEW fails to rename the >> columns >> - Update tab-complete.c >> - Add regression test >> >> > Oh, I just sent the patch to ask, good you do that in all the places. > > One issue I've not addressed yet is about the command tag of >> "ALTER VIEW RENAME COLUMN". Currently "ALTER TABLE" is returned as the tag >> like ALTER MATERIALIZED VIEW RENAME COLUMN, but ISTM that "ALTER VIEW" >> is better. I started the discussion about the command tag of >> "ALTER MATERIALIZED VIEW" at another thread. I will update the patch >> according >> to the result of that discussion. >> >> https://postgr.es/m/CAHGQGwGUaC03FFdTFoHsCuDrrNvFvNVQ6xyd40==p25wvub...@mail.gmail.com >> >> Attached patch contain small change for ALTER MATERIALIZED VIEW. > > Hmm, my small change of "ALTER MATERIALIZED VIEW" does not work in some cases need more work on that. > > >> Regards, >> >> -- >> Fujii Masao >> > > > -- > Ibrar Ahmed > -- Ibrar Ahmed
