361
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) 最後更新：2017-06-08 11:31:56