函数
CREATE OR REPLACE FUNCTION ifnotexist(sqlselect text, sqlexec text)
RETURNS integer AS
$BODY$
DECLARE
exists int;
BEGIN
EXECUTE sqlSelect INTO exists;
IF exists <= 0
THEN
EXECUTE sqlExec;
RETURN 1;
END IF;
RETURN 0;
EXCEPTION
WHEN undefined_table
THEN
return -1;
--存在查询结果,不执行语句.返回1 执行成功,返回0 已经有相同的内容存在,返回-1 操作异常(目前是指无对应的表存在)
--参数1 sqlselect 判断语句
--参数2 sqlexec 执行语句
--执行语句(一) -- 插入初始化数据
--select ifnotexists('select count(*) from t_mm_wei where fa = ''1''','update t_mm_wei set fb = ''........00178645'' where fa = ''1''');
--执行语句(二)-- 创建表
--select ifnotexists('select count(*) from pg_class where relname = ''t_mm_create''',
--'CREATE TABLE t_mm_create(fa integer NOT NULL,fb text,CONSTRAINT t_mm_create_pkey PRIMARY KEY (fa))
--WITH (OIDS=FALSE
--);ALTER TABLE t_mm_create OWNER TO postgres;'
--);
--执行语句(三) -- 增加字段
--select ifnotexists('select count(*) from pg_attribute t0 inner join pg_class t1 on t0.attrelid = t1.oid where t1.relname = ''t_mm_create'' and t0.attname = ''fid''',
--'alter table t_mm_create add fid Integer;');
--执行语句(四) -- 创建索引
--select ifnotexist('select count(*) from pg_stat_user_indexes where indexrelname = ''prefix_storage_btree_index''',
--'CREATE INDEX prefix_storage_btree_index ON portal_deliveryurlprefix USING btree (storagespaceid, storagespacename);');
--判断字段是否存在
--select * from pg_attribute t0 inner join pg_class t1 on t0.attrelid = t1.oid where t1.relname = 't_mm_wei' and t0.attname = 'fa'
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION ifnotexist(text, text) OWNER TO postgres;
CREATE OR REPLACE FUNCTION ifnotexist(sqlselect text, sqlexec text)
RETURNS integer AS
$BODY$
DECLARE
exists int;
BEGIN
EXECUTE sqlSelect INTO exists;
IF exists <= 0
THEN
EXECUTE sqlExec;
RETURN 1;
END IF;
RETURN 0;
EXCEPTION
WHEN undefined_table
THEN
return -1;
--存在查询结果,不执行语句.返回1 执行成功,返回0 已经有相同的内容存在,返回-1 操作异常(目前是指无对应的表存在)
--参数1 sqlselect 判断语句
--参数2 sqlexec 执行语句
--执行语句(一) -- 插入初始化数据
--select ifnotexists('select count(*) from t_mm_wei where fa = ''1''','update t_mm_wei set fb = ''........00178645'' where fa = ''1''');
--执行语句(二)-- 创建表
--select ifnotexists('select count(*) from pg_class where relname = ''t_mm_create''',
--'CREATE TABLE t_mm_create(fa integer NOT NULL,fb text,CONSTRAINT t_mm_create_pkey PRIMARY KEY (fa))
--WITH (OIDS=FALSE
--);ALTER TABLE t_mm_create OWNER TO postgres;'
--);
--执行语句(三) -- 增加字段
--select ifnotexists('select count(*) from pg_attribute t0 inner join pg_class t1 on t0.attrelid = t1.oid where t1.relname = ''t_mm_create'' and t0.attname = ''fid''',
--'alter table t_mm_create add fid Integer;');
--执行语句(四) -- 创建索引
--select ifnotexist('select count(*) from pg_stat_user_indexes where indexrelname = ''prefix_storage_btree_index''',
--'CREATE INDEX prefix_storage_btree_index ON portal_deliveryurlprefix USING btree (storagespaceid, storagespacename);');
--判断字段是否存在
--select * from pg_attribute t0 inner join pg_class t1 on t0.attrelid = t1.oid where t1.relname = 't_mm_wei' and t0.attname = 'fa'
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION ifnotexist(text, text) OWNER TO postgres;