postgresql统一把字段小写转大写

--1创建函数

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
 

-- 2根据条件查询aa_bb表，所有大写的column
select * from information_schema.columns where table_schema = 'public' AND table_catalog = 'ry-vue' and table_name = 'aa_bb';
 
-- 3修改条件后，通过下列语句转换column_name中的大写字母 大小写转换upper/lower
SELECT
    exec('alter table "' || table_name || '" rename column  "' || column_name || '" to "' || upper( column_name ) || '";')
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'public' 
        AND table_catalog = 'ry-vue'
    AND column_name <> upper(column_name)
    AND table_name = 'aa_bb';

4表名转大小写

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);
 

这篇好文章是转载于：学新通技术网