PGSQL：批量替换所有表指定字符串数据

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;