我遇到了这个错误：

[2026-05-28 06:55:51] [42601] ERROR: SELECT查询没有结果数据的目标
[2026-05-28 06:55:51] Hint: 如果你想丢弃结果，请改用动态SQL (EXECUTE)。要返回结果集，请使用临时表或refcursor。
[2026-05-28 06:55:51] 位置：PL/pgSQL函数 "compare_databases" 第20行的SQL语句

when I try to run the procedure shown here, to compare the tables between two DB schemas. The error occurs with the SELECT query within table1_cols and clearly asking for a result set but from the SQL syntax of it, it seems correct.

CREATE OR REPLACE PROCEDURE compare_databases()
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);

        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' AS diff_type, 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' AS diff_type, 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;

解决方案

我的答案针对PostgreSQL；希望Redshift的行为也类似。

你循环中的 SELECT 语句没有查询结果的目标。你可以使用 SELECT <select list> INTO <variable list> FROM ... 将查询的第一行结果存入变量，或者将 SELECT 替换为 PERFORM 以丢弃结果。

然而，在我看来，你似乎想把查询结果返回给用户。要实现这一点，你应该创建一个函数而不是一个过程，将其声明为

CREATE FUNCTION compare_databases()
RETURNS TABLE (diff_type text, column_name text, ...)
AS ...

并通过如下方式返回查询结果：

RETURN QUERY WITH ... SELECT ...;