【PostgreSQL】PostgreSQL重命名字段的函数

---

/* 重命名表字段的函数，可重复执行 */ 
create or replace function rename_column(
  v_schema varchar(50),
  v_table varchar(50),
  v_column_old varchar(50),
  v_column_new varchar(50)
) returns varchar(100)
as $$
declare
  v_flag BOOLEAN;
  v_flag_new BOOLEAN;
  v_sql text :=concat('alter table ',v_schema,'.',v_table,' rename column ',v_column_old,' to ',v_column_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
    join pg_attribute t3 on t1.oid = t3.attrelid
   where attnum > 0
     and t2.nspname=v_schema
     and t1.relname=v_table
     and t3.attname=v_column_old;
  select (case when count(*)=0 then false else true end) into v_flag_new
    from pg_class t1
    join pg_namespace t2 on t1.relnamespace=t2.oid
    join pg_attribute t3 on t1.oid = t3.attrelid
   where attnum > 0
     and t2.nspname=v_schema
     and t1.relname=v_table
     and t3.attname=v_column_new;
  if v_flag and not v_flag_new then
    execute v_sql;
    return '字段重命名成功！';
  ELSIF not v_flag then
    return '源字段不存在，修改失败！';
  ELSIF v_flag_new then
    return '目标字段已存在！';
  else
    return '未知！';
  end if;
end;
$$ language plpgsql;

/* 函数执行范例 */ 
-- 比如这个语句：ALTER TABLE tzq.tzq_log_t RENAME COLUMN log_name TO log_title_name;
-- 执行下面的SQL即可：
-- select rename_column('tzq','tzq_log_t','log_name','log_title_name');