作者
digoal
日期
2017-06-05
标签
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)
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
