当通配符在整个数据库中搜索一条记录时，希望获得架构名、表名和列名

Question

我一直在尝试以下代码，以便在整个数据库中对记录进行通配符搜索。但只返回完全匹配的结果集。

CREATE OR REPLACE FUNCTION search_columns(
needle text,
haystack_tables name[] default '{}',
haystack_schema name[] default '{}'
)
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)
JOIN information_schema.table_privileges p ON
(t.table_name=p.table_name AND t.table_schema=p.table_schema
AND p.privilege_type='SELECT')
JOIN information_schema.schemata s ON
(s.schema_name=t.table_schema)
WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
AND t.table_type='BASE TABLE'
LOOP
FOR rowctid IN
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
schemaname,
tablename,
columnname,
needle
)
LOOP
-- uncomment next line to get some progress report
-- RAISE NOTICE 'hit in %.%', schemaname, tablename;
RETURN NEXT;
END LOOP;
END LOOP;
END;
$$ language plpgsql

这只在记录完全匹配时返回。我需要所有通配符与其模式名、表名和列名相匹配

从search_columns('fo'(中选择*；

Answer 1

您需要构造一个LIKE表达式，而不是为相等性进行比较：

EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text) LIKE %L',
schemaname,
tablename,
columnname,
concat('%', needle, '%')
)

如果要进行不区分大小写的比较，请改用ILIKE。