Date: 2014-09-19 14:45:32 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: -- development

Last updated: 2018-03-29 15:39:07 +0200

Comment 20165

Date: 2014-09-19 14:45:32 +0200
From: Martin van Dinther <<martin.van.dinther>>

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 26180

Date: 2018-02-08 18:43:04 +0100
From: Martin van Dinther <<martin.van.dinther>>

CREATE VIEW functionality (and more) added in changeset
https//devmonetdborg/hg/MonetDB?cmd=changeset;node=2838cc1c63b5

Comment 26182

Date: 2018-02-08 18:49:43 +0100
From: Martin van Dinther <<martin.van.dinther>>

I meant:
CREATE OR REPLACE VIEW functionality (and more) added in changeset
https//devmonetdborg/hg/MonetDB?cmd=changeset;node=2838cc1c63b5

Comment 26302

Date: 2018-03-29 15:39:07 +0200
From: @sjoerdmullender

The Mar2018 version has been released.