通过oracle转pg,表名变成了大写,字段也大写了,后台代码改太麻烦,所以需要转为小写。
-- 创建exec(sqlstring)函数方便执行
CREATE OR REPLACE FUNCTION "public"."exec"("sqlstring" varchar)
RETURNS "pg_catalog"."varchar" AS $BODY$
declare
res varchar(50);
BEGIN
EXECUTE sqlstring;
RETURN 'ok';
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
-- 通过下列语句转换column_name中的大写字母
SELECT
exec('alter table "' || table_name || '" rename column "' || column_name || '" to ' || lower( column_name ) || ';')
FROM
information_schema.COLUMNS
WHERE
table_schema = 'public'
AND column_name <> lower(column_name)
--AND table_name like '%';
-- 查询表名中的大写
SELECT * FROM information_schema.TABLES
WHERE table_schema = 'public' AND table_catalog = '表名' AND table_name <> lower( table_name );
-- 修改表名中的大写为小写
SELECT
exec ( 'alter table "' || table_name || '" rename to ' || lower( table_name ) || ';' )
FROM
information_schema.tables
WHERE
table_schema='public'
and table_catalog = '表名'
and table_name <> lower(table_name);
亲测,很好用