1. 替换所有表中的指定字符串
指定了schema为public,其它schema需要替换:
-- source_char 替换的源字符串
-- target_char 替换的目标字符串
-- un_replace_table 无需替换的表名数组
-- replace_data_type 需要替换的字段类型数组
CREATE
OR REPLACE FUNCTION replace_data_un_in_tables (source_char VARCHAR, target_char VARCHAR, un_replace_table VARCHAR[], replace_data_type VARCHAR[]) RETURNS BOOLEAN AS $$ BEGIN
--创建游标
DECLARE
table_one RECORD;
table_group CURSOR FOR SELECT TABLE_NAME, COLUMN_NAME
FROM
information_schema.COLUMNS
WHERE
--这里指定了schema为public,如果表在其他schema中,可以替换
TABLE_NAME IN ( SELECT tablename FROM pg_tables WHERE schemaname = 'public' )
--指定字段类型
AND data_type = ANY(replace_data_type)
--这里指定不需要更新的表
AND NOT (TABLE_NAME = ANY(un_replace_table));
--打开游标
BEGIN
OPEN table_group;
LOOP
FETCH table_group INTO table_one;
EXIT
WHEN NOT FOUND;
raise notice 'now update is %', table_one."table_name";
EXECUTE format ( 'UPDATE "%s"
SET "%s" = REPLACE ( "%s", ''%s'', ''%s'' );', table_one."table_name" , table_one."column_name", table_one."column_name", source_char, target_char );
END LOOP;
CLOSE table_group;
RETURN TRUE;
END;
END;
$$ LANGUAGE plpgsql;
-- 执行测试用例
BEGIN;
SELECT replace_data_un_in_tables ( '好的好的', '对的对的', array['user']::VARCHAR[], array['character varying', 'text']::VARCHAR[]);
ROLLBACK;
如果是替换指定几张表中的数据,可以将代码调整为下面这样:
-- source_char 替换的源字符串
-- target_char 替换的目标字符串
-- replace_table 需要替换的表名数组
-- replace_data_type 需要替换的字段类型数组
CREATE
OR REPLACE FUNCTION replace_data_in_tables (source_char VARCHAR, target_char VARCHAR, replace_table VARCHAR[], replace_data_type VARCHAR[]) RETURNS BOOLEAN AS $$ BEGIN
--创建游标
DECLARE
table_one RECORD;
table_group CURSOR FOR SELECT TABLE_NAME, COLUMN_NAME
FROM
information_schema.COLUMNS
WHERE
--这里指定了schema为public,如果表在其他schema中,可以替换
TABLE_NAME IN ( SELECT tablename FROM pg_tables WHERE schemaname = 'public' )
--指定字段类型
AND data_type = ANY(replace_data_type)
--这里指定需要更新的表
AND TABLE_NAME = ANY(replace_table);
--打开游标
BEGIN
OPEN table_group;
LOOP
FETCH table_group INTO table_one;
EXIT
WHEN NOT FOUND;
raise notice 'now update is %', table_one."table_name";
EXECUTE format ( 'UPDATE "%s"
SET "%s" = REPLACE ( "%s", ''%s'', ''%s'' );', table_one."table_name" , table_one."column_name", table_one."column_name", source_char, target_char );
END LOOP;
CLOSE table_group;
RETURN TRUE;
END;
END;
$$ LANGUAGE plpgsql;
-- 执行测试用例
BEGIN;
SELECT replace_data_in_tables ( '好的好的', '对的对的', array['user']::VARCHAR[], array['character varying', 'text']::VARCHAR[]);
ROLLBACK;