Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for: create OR REPLACE view ... #3574

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Add support for: create OR REPLACE view ... #3574

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant