Bug 3574 - Add support for: create OR REPLACE view ...
Summary: Add support for: create OR REPLACE view ...
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: x86_64 (amd64/em64t) Linux
: Normal enhancement
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-09-19 14:45 CEST by Martin van Dinther
Modified: 2018-03-29 15:39 CEST (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther 2014-09-19 14:45:32 CEST
User-Agent:       Mozilla/5.0 (X11; Linux x86_64; rv:32.0) Gecko/20100101 Firefox/32.0
Build Identifier: 

It is not possible to change an existing view using SQL:
CREATE OR REPLACE view ...

Adding support for this would be very convenient, especially when views are based on views. It can save a lot of manual work and prevent loss of view definitions.


Reproducible: Always

Steps to Reproduce:
sql>CREATE TABLE example (id INTEGER primary key, name VARCHAR(50) NOT NULL, created_on DATE NOT NULL);

sql>CREATE VIEW  example_vw1 AS select cast(id as varchar(10)) as "ID", name as "Name" from example;
sql>select * from example_vw1;

sql>CREATE VIEW  example_vw2 AS select "ID" || ': ' || "Name" as "ID: Name" from example_vw1;
sql>select * from example_vw2;

sql>CREATE OR REPLACE VIEW  example_vw1 AS select cast(id as varchar(10)) as "ID", name as "Name", created_on from example;

sql>DROP VIEW  example_vw1;

sql>DROP VIEW  example_vw1 cascade;

sql>CREATE VIEW  example_vw1 AS select cast(id as varchar(10)) as "ID", name as "Name", created_on from example;
sql>select * from example_vw1;
sql>select * from example_vw2;

Actual Results:  
bash-4.2$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'demo'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>CREATE TABLE example (id INTEGER primary key, name VARCHAR(50) NOT NULL, created_on DATE NOT NULL);
operation successful (26.431ms)
sql>CREATE VIEW  example_vw1 AS select cast(id as varchar(10)) as "ID", name as "Name" from example;
operation successful (4.496ms)
sql>select * from example_vw1;
+----+------+
| ID | Name |
+====+======+
+----+------+
0 tuples (1.328ms)
sql>CREATE VIEW  example_vw2 AS select "ID" || ': ' || "Name" as "ID: Name" from example_vw1;
operation successful (5.689ms)
sql>select * from example_vw2;
+----------+
| ID: Name |
+==========+
+----------+
0 tuples (1.510ms)
sql>
sql>CREATE OR REPLACE VIEW  example_vw1 AS select cast(id as varchar(10)) as "ID", name as "Name", created_on from example;
syntax error, unexpected OR, expecting INDEX in: "create or"
sql>DROP VIEW  example_vw1;
DROP VIEW: cannot drop view 'example_vw1', there are database objects which depend on it
sql>DROP VIEW  example_vw1 cascade;
operation successful (2.758ms)
sql>CREATE VIEW  example_vw1 AS select cast(id as varchar(10)) as "ID", name as "Name", created_on from example;
operation successful (7.306ms)
sql>select * from example_vw1;
+----+------+------------+
| ID | Name | created_on |
+====+======+============+
+----+------+------------+
0 tuples (2.599ms)
sql>select * from example_vw2;
SELECT: no such table 'example_vw2'
sql>


Expected Results:  
Should be able to execute
CREATE OR REPLACE VIEW  example_vw1 AS select cast(id as varchar(10)) as "ID", name as "Name", created_on from example;

It reliefs the user to do
 DROP VIEW example_vw1;
or in case of dependencies:
 DROP VIEW example_vw1 cascade;
and prevents the need to recreate all the dependent views again.
Comment 1 Martin van Dinther 2018-02-08 18:43:04 CET
CREATE VIEW functionality (and more) added in changeset
 https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2838cc1c63b5
Comment 2 Martin van Dinther 2018-02-08 18:49:43 CET
I meant:
CREATE OR REPLACE VIEW functionality (and more) added in changeset
 https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2838cc1c63b5
Comment 3 Sjoerd Mullender cwiconfidential 2018-03-29 15:39:07 CEST
The Mar2018 version has been released.