标签
PostgreSQL , Greenplum , DDL , IF NOT EXISTS
背景
当对象存在时，不创建；当对象不存在时，创建。
在数据库中使用IF NOT EXISTS语法进行判断。
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
有一些较老的版本，可能不支持IF NOT EXISTS语法，那么可以使用UDF实现类似的功能。
例如Greenplum：
create or replace function ddl_ine(sql text) returns int2 as $$
declare
begin
execute sql;
return 0; -- 返回0表示正常
exception when duplicate_table then
raise notice '%', SQLERRM;
return 1; -- 返回1表示已存在
when others then
raise notice '%ERROR: % %create table error: %', chr(10), SQLERRM, chr(10), sql;
return 2; -- 返回2表示DDL其他错误
end;
$$ language plpgsql strict;
测试
postgres=# select ctbl('create table c(id int)');
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CONTEXT: SQL statement "create table c(id int)"
PL/pgSQL function "ctbl" line 3 at execute statement
NOTICE: relation "c" already exists
ctbl
------
1
(1 row)
postgres=# select ctbl('create table e(id int)');
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CONTEXT: SQL statement "create table e(id int)"
PL/pgSQL function "ctbl" line 3 at execute statement
ctbl
------
0
(1 row)
postgres=# select ctbl('create table e(id int9)');
NOTICE:
ERROR: type "int9" does not exist
DETAIL: create table error: create table e(id int9)
ctbl
------
2
(1 row)
