PostgreSQL 调用存储过程更新表字段类型

凌霄玉阶非所愿

已于 2022-09-29 10:45:34 修改

阅读量1.1k

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库 Postgresql 文章标签： postgresql 数据库

于 2022-09-09 16:17:22 首次发布

本文链接：https://blog.csdn.net/weixin_42064877/article/details/126784473

数据库同时被 2 个专栏收录

43 篇文章

订阅专栏

Postgresql

13 篇文章

订阅专栏

本次只针对个人问题解决，贴出来以作记录
1、问题说明
oracle -> Postgresql迁移库之后遇到一个问题，原本在oracle库中表字段类型为number转到Postgresql中之后变成了numeric(1000,53)。

下图所示：
在这里插入图片描述

在这里插入图片描述

后台登录查询此表报错为：
System.Exception: Reset() called on connector with state Fetching

故写此存储过程进行批量更新下：

CREATE OR REPLACE PROCEDURE PRO_ALTERFIED() LANGUAGE PLPGSQL AS $$
declare
i record;
fied varchar;
 pgSqlScript text;
begin
for i in (SELECT A.TABLENAME,
	B.RELNAME,
    c.attname,
	FORMAT_TYPE(C.ATTTYPID,C.ATTTYPMOD) AS TYPE
FROM PG_TABLES AS A,
  PG_CLASS AS B,
  PG_ATTRIBUTE AS C
WHERE A.SCHEMANAME = CURRENT_SCHEMA()
  AND A.TABLENAME = B.RELNAME
  AND c.ATTRELID = b.OID
    and FORMAT_TYPE(C.ATTTYPID,C.ATTTYPMOD) ='numeric(1000,53)' and a.tablename='t_hospital') loop
    fied =i.attname;
    pgSqlScript:='ALTER TABLE t_hospital  ALTER COLUMN '||fied || ' TYPE numeric(1000,4)';
    execute pgSqlScript;
    commit;
    RAISE NOTICE  '本次更新数据条数：%',fied; --打印输出
    end loop;
end;
$$;

--调用
call pro_alterfied();

调用完成后即打印输出内容如下：
在这里插入图片描述

更新完成之后再次查看表里字段类型：


SELECT A.TABLENAME,--表名
	B.RELNAME, --表名
	FORMAT_TYPE(C.ATTTYPID,C.ATTTYPMOD) AS TYPE,--类型
    c.attname as name, --字段
    col_description(c.attrelid,c.attnum) as comment --注释
FROM PG_TABLES AS A,
	PG_CLASS AS B,
	PG_ATTRIBUTE AS C
WHERE A.SCHEMANAME = CURRENT_SCHEMA()
	AND A.TABLENAME = B.RELNAME
	AND c.ATTRELID = b.OID 
    and FORMAT_TYPE(C.ATTTYPID,C.ATTTYPMOD) ='numeric(1000,4)'
    and a.tablename='t_hospital';