【PostgreSQL】PostgreSQL重命名表的存储过程，可重复执行

【PostgreSQL】PostgreSQL重命名表的存储过程，可重复执行

/* 重命名表的存储过程，可重复执行 */ 
drop PROCEDURE if exists    rename_table_p;
create or replace PROCEDURE rename_table_p(
  v_schema varchar(50),
  v_table_old varchar(50),
  v_table_new varchar(50)
)
language plpgsql
AS $$
declare
  v_flag BOOLEAN;
  v_sql text :=concat('alter table ', v_schema, '.', v_table_old, ' rename ', ' to ', v_table_new);
begin
  select (case when count(*)=0 then false else true end) into v_flag
    from pg_class t1
    join pg_namespace t2 on t1.relnamespace=t2.oid
   where t2.nspname=v_schema
     and t1.relname=v_table_old;
  if v_flag then
    execute v_sql;
    RAISE NOTICE 'Table % renamed to % successfully !', v_table_old, v_table_new;
  else
    RAISE NOTICE 'The table % does not exist. Renaming failed.', v_table_old;
  end if;
end;
$$;
/* 存储过程执行范例 */ 
-- 比如这个语句：ALTER TABLE plan.test_1 RENAME TO test_2;
-- 执行下面的SQL即可：
-- call rename_table_p('plan','test_1','test_2');