The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

Hi all,

I like the new CREATE OR REPLACE clause for views, procedures and the like introduced with SA 11.0.1. My thought was that in case of an already existing object, the behaviour would be identical to an ALTER statement.

I just found out that this seems not to be the case:

Whereas an ALTER VIEW will leave the table_id and object_id (and the creation_time in SYSOBJECT) unchanged, a replaced view gets new values there. So a CREATE OR REPLACE seems to be a DROP statement combined with a CREATE statement and regenerated permissions.

Of course, the difference is somewhat marginal; however, I thought CREATE OR REPLACE would be a handy substitute to the following common script construct:

CALL sa_make_object('view','MyView');
ALTER VIEW MyView AS SELECT ...;

Is this difference by design? And are there other slight differences between CREATE OR REPLACE and ALTER with respect to existing objects?

asked 19 Mar '10, 08:56

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 14 Jan '15, 17:34

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264

We already have ALTER so why create another syntactic representation for it? DROP and re-CREATE is an extremely common requirement, completely different from ALTER (albeit less different for some object types like VIEW)... it doesn't regenerate permissions, does it? Hope not, I want to use this construction in "recreate objects from scratch" scenarios.

(19 Mar '10, 09:55) Breck Carter
1

CREATE OR REPLACE is my choice if I don't want to care if the object to be created exists already (no explicit DROP VIEW or DROP PROCEDURE). However up to now I've never bothered bout ID's

(19 Mar '10, 10:54) Reimer Pods
1

@Breck: From the docs: "Existing permissions are preserved when you use the OR REPLACE clause." AFAIk, that's the main difference (and advantage IMHO) between the new syntax and DROP/CREATE.

(19 Mar '10, 17:09) Volker Barth
1

@Reimer: I normally don't care about IDs of database objects, either, but I often use "SELECT * from SYSTABLE ORDER BY ID" to check for newly generated tables/views (and would not expect those REPLACEd ones there), and I do care about the creation_time catalog entry.

(19 Mar '10, 17:12) Volker Barth

Well, that old question is still unanswered...

(15 Jan '15, 03:40) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×28
×2
×1

question asked: 19 Mar '10, 08:56

question was seen: 2,446 times

last updated: 15 Jan '15, 03:40