PostgreSQL函数改造需求：新增rowvalue列及查询无指定值的表

下面是满足需求的改造版本，同时实现了新增rowvalue列和支持查询不包含指定值的功能：

DROP FUNCTION IF EXISTS pcm_search_columns(text, name[], name[], boolean);

CREATE OR REPLACE FUNCTION pcm_search_columns(
    needle text,
    haystack_tables name[] DEFAULT '{}',
    haystack_schema name[] DEFAULT '{}',
    exclude boolean DEFAULT false  -- 新增参数：true表示查询不包含指定值的行
)
RETURNS TABLE(schemaname text, tablename text, columnname text, rowctid text, rowvalue text)  -- 新增rowvalue列
AS $$
DECLARE
    rec record;  -- 用record接收动态SQL的多列结果
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 = 'el_div'
            AND (c.table_schema = ANY(haystack_schema) OR haystack_schema = '{}')
            AND t.table_type = 'BASE TABLE'
            AND c.column_name = 'grp'
    LOOP
        -- 根据exclude参数动态生成条件，同时查询id和列值
        FOR rec IN EXECUTE format(
            'SELECT id, cast(%I as text) AS rowvalue FROM %I.%I WHERE cast(%I as text) %s %L',
            columnname,
            schemaname,
            tablename,
            columnname,
            CASE WHEN exclude THEN '<>' ELSE '=' END,
            needle
        )
        LOOP
            rowctid := rec.id;
            rowvalue := rec.rowvalue;
            RETURN NEXT;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

关键改动说明 #

• 新增rowvalue返回列：直接展示grp列的实际值，无需额外查询就能看到匹配（或不匹配）的内容
• 新增exclude控制参数：默认false保留原查询逻辑（找包含指定值的行），设为true时切换为查询不包含指定值的行
• 动态SQL优化：用format函数安全拼接SQL，根据参数自动切换=或<>运算符，同时获取id和列值
• 结果接收调整：改用record类型接收多列查询结果，再映射到返回表的对应字段

使用示例 #

1. 查询包含值'5'的行（原逻辑）：

SELECT * FROM pcm_search_columns('5', '{}');

2. 查询不包含值'5'的行：

SELECT * FROM pcm_search_columns('5', '{}', '{}', true);

内容的提问来源于stack exchange，提问作者Mayoh