PostgreSQL存储过程报错42601：SELECT查询无结果目标求助

PostgreSQL的PL/pgSQL不允许在存储过程的代码块中直接执行无结果接收目标的SELECT语句。你在LOOP里的联合查询既没有把结果赋值给变量/记录，也没有通过合适的方式返回给调用者，因此触发了42601错误。

以下两种常见方式可解决问题，按需选择：

方式1：改为返回TABLE类型的函数 #

这是最直接的实现方式，适合需要直接获取比较结果的场景：

CREATE OR REPLACE FUNCTION compare_databases()
RETURNS TABLE(
    diff_type TEXT,
    column_name VARCHAR,
    ordinal_position INTEGER,
    data_type VARCHAR,
    character_maximum_length INTEGER,
    numeric_precision INTEGER,
    numeric_scale INTEGER,
    is_nullable VARCHAR
) AS $$
DECLARE
    v_table_name VARCHAR(50);
    tmp_tab_rec RECORD;
BEGIN
    FOR tmp_tab_rec IN (
        SELECT table_schema, table_name FROM information_schema.tables
             WHERE table_catalog = 'nonprod'
               AND table_schema NOT LIKE 'pg_%'
               AND table_schema NOT LIKE 'dbt_%'
               AND table_schema NOT LIKE 'models%'
               AND table_schema NOT IN ('information_schema', 'control')
               AND table_name LIKE 'tmp_%'
               AND table_type = 'BASE TABLE'
            ORDER BY table_schema, table_name
    )
    LOOP
        v_table_name := SPLIT_PART(tmp_tab_rec.table_name, '_', 2);

        RETURN QUERY
        WITH table1_cols AS ( --nonprod table columns
            SELECT
                column_name,
                ordinal_position,
                data_type,
                character_maximum_length,
                numeric_precision,
                numeric_scale,
                is_nullable
        FROM information_schema.columns
        WHERE table_schema = tmp_tab_rec.table_schema
          AND table_name   = v_table_name
        ),
        table2_cols AS ( --warehouse table columns
            SELECT
                column_name,
                ordinal_position,
                data_type,
                character_maximum_length,
                numeric_precision,
                numeric_scale,
                is_nullable
        FROM information_schema.columns
        WHERE table_schema = tmp_tab_rec.table_schema
          AND table_name   = tmp_tab_rec.table_name
        )
        SELECT 'COLUMN_EXISTS_ONLY_IN_WAREHOUSE'::TEXT, t2.*
        FROM table2_cols t2
        LEFT JOIN table1_cols t1
               ON t1.column_name = t2.column_name
              AND t1.data_type   = t2.data_type
              AND t1.is_nullable = t2.is_nullable
        WHERE t1.column_name IS NULL

        UNION ALL

        SELECT 'COLUMN_DOES_NOT_MATCH'::TEXT, t1.*
        FROM table1_cols t1
        JOIN table2_cols t2
             ON t1.column_name = t2.column_name
        WHERE t1.data_type   <> t2.data_type
           OR t1.is_nullable <> t2.is_nullable
           OR t1.character_maximum_length <> t2.character_maximum_length
           OR t1.numeric_precision <> t2.numeric_precision
           OR t1.numeric_scale <> t2.numeric_scale

        ORDER BY diff_type, ordinal_position;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

使用方式：直接调用函数即可返回结果集

SELECT * FROM compare_databases();

方式2：在存储过程中用临时表收集结果 #

如果必须使用存储过程，可通过临时表存储每次循环的比较结果：

CREATE OR REPLACE PROCEDURE compare_databases()
AS $$
DECLARE
    v_table_name VARCHAR(50);
    tmp_tab_rec RECORD;
BEGIN
    -- 创建临时表存储结果，会话结束自动删除
    CREATE TEMP TABLE IF NOT EXISTS schema_diff_results (
        diff_type TEXT,
        column_name VARCHAR,
        ordinal_position INTEGER,
        data_type VARCHAR,
        character_maximum_length INTEGER,
        numeric_precision INTEGER,
        numeric_scale INTEGER,
        is_nullable VARCHAR
    ) ON COMMIT DROP;

    -- 清空临时表，避免重复执行时遗留数据
    TRUNCATE TABLE schema_diff_results;

    FOR tmp_tab_rec IN (
        SELECT table_schema, table_name FROM information_schema.tables
             WHERE table_catalog = 'nonprod'
               AND table_schema NOT LIKE 'pg_%'
               AND table_schema NOT LIKE 'dbt_%'
               AND table_schema NOT LIKE 'models%'
               AND table_schema NOT IN ('information_schema', 'control')
               AND table_name LIKE 'tmp_%'
               AND table_type = 'BASE TABLE'
            ORDER BY table_schema, table_name
    )
    LOOP
        v_table_name := SPLIT_PART(tmp_tab_rec.table_name, '_', 2);

        -- 将查询结果插入临时表
        INSERT INTO schema_diff_results
        WITH table1_cols AS ( --nonprod table columns
            SELECT
                column_name,
                ordinal_position,
                data_type,
                character_maximum_length,
                numeric_precision,
                numeric_scale,
                is_nullable
        FROM information_schema.columns
        WHERE table_schema = tmp_tab_rec.table_schema
          AND table_name   = v_table_name
        ),
        table2_cols AS ( --warehouse table columns
            SELECT
                column_name,
                ordinal_position,
                data_type,
                character_maximum_length,
                numeric_precision,
                numeric_scale,
                is_nullable
        FROM information_schema.columns
        WHERE table_schema = tmp_tab_rec.table_schema
          AND table_name   = tmp_tab_rec.table_name
        )
        SELECT 'COLUMN_EXISTS_ONLY_IN_WAREHOUSE'::TEXT, t2.*
        FROM table2_cols t2
        LEFT JOIN table1_cols t1
               ON t1.column_name = t2.column_name
              AND t1.data_type   = t2.data_type
              AND t1.is_nullable = t2.is_nullable
        WHERE t1.column_name IS NULL

        UNION ALL

        SELECT 'COLUMN_DOES_NOT_MATCH'::TEXT, t1.*
        FROM table1_cols t1
        JOIN table2_cols t2
             ON t1.column_name = t2.column_name
        WHERE t1.data_type   <> t2.data_type
           OR t1.is_nullable <> t2.is_nullable
           OR t1.character_maximum_length <> t2.character_maximum_length
           OR t1.numeric_precision <> t2.numeric_precision
           OR t1.numeric_scale <> t2.numeric_scale;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

使用方式：

1. 调用存储过程：

CALL compare_databases();

2. 查询临时表获取结果：

SELECT * FROM schema_diff_results ORDER BY diff_type, ordinal_position;

• 方式1使用RETURN QUERY直接返回查询结果，是PL/pgSQL函数返回结果集的标准写法
• 方式2通过INSERT INTO将查询结果存入临时表，解决了“无结果接收目标”的核心问题

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