Postgre 固定值查询.sql

– 匹配结果表
CREATE TABLE COUNTRY_DATA
(
“TABLE_NAME” VARCHAR(512) NULL,
“COLUMN_NAME” VARCHAR(512) NULL
);

– 扫描表
CREATE TABLE COUNTRY_DATA_TABLE_NAME
(
“TABLE_NAME” VARCHAR(512) NULL,
“COLUMN_NAME” VARCHAR(512) NULL,
“DTCREATEDTIME” timestamp NULL
);

DO $$
DECLARE
table_name text;
column_name text;
query text;
result_count integer;
BEGIN
FOR table_name, column_name IN
SELECT
c.table_name,
c.column_name
FROM
information_schema.columns c
WHERE
c.table_schema = ‘xxxxxx’ – 可选：指定特定的模式
AND c.table_catalog = current_database() – 可选：指定特定的数据库
and c.data_type not IN (‘numeric’, ‘timestamp’, ‘text’)
and c.table_name not like (‘qrtz_%’)
and c.table_name not like (‘ids_%’)
and c.table_name not in (‘interface_log’,‘country_data’,‘country_data_table_name’)
and c.COLUMN_NAME not in (‘guid’, ‘st_created_by’, ‘st_updated_by’, ‘st_data_status’)
order by c.table_name,c.column_name
LOOP
query := format(
‘SELECT COUNT(*) FROM %I WHERE %I::text LIKE ‘’%%AEOCN2101933478%%’‘’,-- 需要查询的值
table_name,
column_name
);

            EXECUTE query INTO result_count;
                insert into COUNTRY_DATA_TABLE_NAME values (table_name, column_name, now());
            IF result_count > 0 THEN
               insert into COUNTRY_DATA values (table_name, column_name);
            END IF;
        END LOOP;
END$$;