DDL statement extension: `create if not exists and `drop if exists`

Wiki (en)
1

Go to Dutch version

New DDL statement extensions create if not exists and drop if exists` make installation scripts and session-bound temporary tables more expressive and easier to use.

Create Object

To create a table, procedure, function or view, the available options were:

create [table|procedure|function|view] NAME

and

create or replace [table|procedure|function|view] NAME

This is extended by the syntax:

create [table|procedure|function|view] if not exists NAME

which only creates the object when it did not previously exist.

For instance, when an application module on Invantive Cloud needs to cache database contents in a session-specific temporary table you can now use:

begin
   ...
  create table if not exists MyCache@InMemoryStorage
  as
  select *
  from   slow_table
  ;
  select ...
  into   ...
  from   MyCache@InMemoryStorage
  where  ...
  ;
  ...
end;

Drop Object

To drop a table, procedure, function or view, the only variant was:

drop [table|procedure|function|view] NAME

This is extended by the syntax:

drop [table|procedure|function|view] if exists NAME

which only drops the object when it did previously existed.

For instance, when an installation script needs to drop a previous version when available, the script can now use:

drop table if exists MyObsoletedTable@SqlServer

Availability

These extensions on DDL statements are available starting 24.0.778 and 25.0.42.