使用sql语句对pgsql数据库某些字段脱敏，及其关联表该字段

找到所有该字段的表名

SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'public' -- 替换为你的模式名称
  AND column_name = 'product_code' -- 替换你的列名
  AND table_name IN (
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'public' -- 替换为你的模式名称
      AND table_type = 'BASE TABLE'
      AND table_name LIKE 'A类表1_%' 
  		or table_name LIKE 'A类表2_%' -- 条件
  );

优化后的SQL

        SELECT t.table_name
	    FROM information_schema.columns c
	      JOIN information_schema.tables t ON c.table_name = t.table_name
	    WHERE c.table_schema = 'public' -- 替换为你的模式名称
	      AND c.column_name = 'product_code'
	      AND t.table_schema = 'public' -- 替换为你的模式名称
	      AND t.table_type = 'BASE TABLE'
	      AND (t.table_name LIKE 'A类表1_%' OR t.table_name LIKE 'A类表2_%')

2.pgsql FOR循环

DO $$
DECLARE
  row_data RECORD;
BEGIN
  FOR row_data IN SELECT column1, column2 FROM your_table LOOP
    -- 在这里编写你的逻辑
    -- 例如，打印每一行的值
    RAISE NOTICE 'Column1: %, Column2: %', row_data.column1, row_data.column2;
  END LOOP;
END $$;

以上组合并脱敏

DO $$
DECLARE
  table_name TEXT;
BEGIN
  FOR table_name IN
        SELECT t.table_name
	    FROM information_schema.columns c
	      JOIN information_schema.tables t ON c.table_name = t.table_name
	    WHERE c.table_schema = 'public' -- 替换为你的模式名称
	      AND c.column_name = 'product_code'
	      AND t.table_schema = 'public' -- 替换为你的模式名称
	      AND t.table_type = 'BASE TABLE'
	      AND 条件1...
  LOOP
    EXECUTE format('
      update %I set product_code = REPLACE(product_code,SUBSTR(product_code,1,4),''*'')
    ', table_name);
  END LOOP;
END $$;

没有关联表存储起字段，直接脱敏

– 1.查询字段最短长度

SELECT MIN(LENGTH(name)) AS shortest_length
FROM 表名1;

– 2.修改字段，进行脱敏

UPDATE 表名1 SET name = 
        CASE 
          WHEN length(name) > 8 THEN '**' || SUBSTRING(name, 3,5) || '**'
		  WHEN length(name) > 3 THEN left(name, 3) || '**'
          ELSE left(name, 1) || '**'
          END