RISEBY
如果有人认为它可以帮助你。这是@DanielVérité的函数，另一个参数接受可以在搜索中使用的列的名称。这样可以减少处理时间。至少在我的测试中它减少了很多。CREATE OR REPLACE FUNCTION search_columns( needle text, haystack_columns name[] default '{}', haystack_tables name[] default '{}', haystack_schema name[] default '{public}')RETURNS table(schemaname text, tablename text, columnname text, rowctid text)AS $$begin FOR schemaname,tablename,columnname IN SELECT c.table_schema,c.table_name,c.column_name FROM information_schema.columns c JOIN information_schema.tables t ON (t.table_name=c.table_name AND t.table_schema=c.table_schema) WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}') AND c.table_schema=ANY(haystack_schema) AND (c.column_name=ANY(haystack_columns) OR haystack_columns='{}') AND t.table_type='BASE TABLE' LOOP EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L', schemaname, tablename, columnname, needle ) INTO rowctid; IF rowctid is not null THEN RETURN NEXT; END IF; END LOOP;END;$$ language plpgsql;Bellow是上面创建的search_function的使用示例。SELECT * FROM search_columns('86192700' , array(SELECT DISTINCT a.column_name::name FROM information_schema.columns AS a INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name) WHERE a.column_name iLIKE '%cep%' AND b.table_type = 'BASE TABLE' AND b.table_schema = 'public' ) , array(SELECT b.table_name::name FROM information_schema.columns AS a INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name) WHERE a.column_name iLIKE '%cep%' AND b.table_type = 'BASE TABLE' AND b.table_schema = 'public'));