-- 创建或删除数据库对象
-- operate: create | drop
-- obj_type: schema | table | column | check_constraint | unique_constraint | foreign_constraint | exclude_constraint |
--           index | view | sequence | function | trigger | procedure | user | role
-- obj_num: 数量
CREATE OR REPLACE FUNCTION operate_database_object(operate varchar, obj_type varchar, obj_num integer)
	RETURNS void
	LANGUAGE 'plpgsql'
	COST 100
	VOLATILE
AS $BODY$
declare
	v_idx integer := 0;
	v_obj_name varchar := '';
	v_sql varchar := '';
	v_sql1 varchar := '';
	v_sql2 varchar := '';
begin
 
	v_obj_name = 'my_' || obj_type || '_';
 
	if operate = 'create' then
		v_sql1 = 'create ' || obj_type || ' ' || v_obj_name;
		if obj_type = 'schema' then
		else if obj_type = 'table' then
			v_sql2 = '(id int primary key, name varchar(100))';
		else if obj_type = 'column' then
			v_sql1 = 'alter table my_table_1 add id_';
			v_sql2 = 'int';
		else if obj_type = 'check_constraint' then
			v_sql1 = 'alter table my_table_1 add constraint ' || obj_type || '_';
			v_sql2 = 'check(id_1 > 0)';
		else if obj_type = 'unique_constraint' then
			v_sql1 = 'alter table my_table_1 add constraint ' || obj_type || '_';
			v_sql2 = 'unique(id_1)';
		else if obj_type = 'foreign_constraint' then
			v_sql1 = 'alter table my_table_1 add constraint ' || obj_type || '_';
			v_sql2 = 'foreign key (id) references my_table_2(id);';
		else if obj_type = 'exclude_constraint' then
			v_sql1 = 'alter table my_table_1 add constraint ' || obj_type || '_';
			v_sql2 = 'exclude using btree(id with =)';
		else if obj_type = 'index' then
			v_sql2 = 'on my_table_1(id)';
		else if obj_type = 'view' then
			v_sql2 = 'as select id, name from my_table_1';
		else if obj_type = 'sequence' then
			v_sql2 = 'cycle increment 1 start 1 minvalue 1 maxvalue 100 cache 10;';
		else if obj_type = 'function' then
			v_sql2 = '() returns trigger language plpgsql as $$ begin return new; end; $$;';
		else if obj_type = 'trigger' then
			v_sql2 = 'after delete ON my_table_1 execute function my_function_1();';
		else if obj_type = 'procedure' then
			v_sql2 = '() as $$ begin end; $$ language plpgsql;';
		else if obj_type = 'user' then
			v_sql2 = 'with password ''123456;''';
		else if obj_type = 'role' then
		end if; end if; end if; end if; end if; end if; end if; end if;
        end if; end if; end if; end if; end if; end if; end if;
 
	else if operate = 'drop' then
		v_sql1 = 'drop ' || obj_type || ' if exists ' || v_obj_name;
		if obj_type = 'trigger' then
			v_sql2 = 'on my_table_1';
		else if obj_type = 'column' then
			v_sql1 = 'alter table my_table_1 drop column if exists id_';
		else if position('constraint' in obj_type) > 0 then
			v_sql1 = 'alter table my_table_1 drop constraint if exists ' || obj_type || '_';
		end if; end if; end if;
	end if; end if;
 
  	while v_idx < obj_num loop
  		v_idx = v_idx + 1;
		v_sql = v_sql1 || v_idx || ' ' || v_sql2;
  		EXECUTE v_sql;
  	end loop;
end
$BODY$;

 
-- 调用
select operate_database_object('create', 'schema', 20);
select operate_database_object('drop', 'schema', 20);
select operate_database_object('create', 'table', 20);
select operate_database_object('drop', 'table', 20);
select operate_database_object('create', 'column', 20);
select operate_database_object('drop', 'column', 20);
-- 向表中批量插入数据
insert into my_table_1(id, name, id_20) select generate_series(1, 100), md5(random()::text), generate_series(1, 100);
delete from my_table_1;
select operate_database_object('create', 'check_constraint', 20);
select operate_database_object('drop', 'check_constraint', 20);
select operate_database_object('create', 'unique_constraint', 20);
select operate_database_object('drop', 'unique_constraint', 20);
select operate_database_object('create', 'foreign_constraint', 20);
select operate_database_object('drop', 'foreign_constraint', 20);
select operate_database_object('create', 'exclude_constraint', 20);
select operate_database_object('drop', 'exclude_constraint', 20);
select operate_database_object('create', 'index', 20);
select operate_database_object('drop', 'index', 20);
select operate_database_object('create', 'view', 20);
select operate_database_object('drop', 'view', 20);
select operate_database_object('create', 'sequence', 20);
select operate_database_object('drop', 'sequence', 20);
select operate_database_object('create', 'function', 20);
select operate_database_object('drop', 'function', 20);
select operate_database_object('create', 'trigger', 20);
select operate_database_object('drop', 'trigger', 20);
select operate_database_object('create', 'procedure', 20);
select operate_database_object('drop', 'procedure', 20);
select operate_database_object('create', 'user', 20);
select operate_database_object('drop', 'user', 20);
select operate_database_object('create', 'role', 20);
select operate_database_object('drop', 'role', 20);