PostgreSQL UDF实现IF NOT EXISTS语法

标签

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)