PostgreSQL动态查询返回结果集报错：无结果数据目标

问题描述 #

尝试运行动态SQL查询返回结果集时遇到两种异常情况：

1. 执行以下代码，运行成功但无任何返回：

DO $$
<<outerblock>>
DECLARE 
    p_sql text;
BEGIN
    p_sql := 'Select * from public.tbl_song;';
    Execute p_sql ;
END $$

2. 执行以下代码，报错“query has no destination for result data”：

DO $$
<<outerblock>>
DECLARE 
    p_sql text;
BEGIN
    p_sql := 'DO 
                $inner$ 
                BEGIN
                Select * from public.tbl_song;
                END; 
                $inner$;';
    Execute p_sql ;
END $$

仅了解到用record变量存储单行数据，不清楚如何返回多行结果集。

核心原因 #

• DO语句的本质限制：PostgreSQL的DO块是用来执行无返回值的匿名PL/pgSQL代码的，设计目标是执行数据修改、对象创建等操作，无论内部执行什么SELECT语句，都不会向外部返回结果集，这是第一个例子无输出的根本原因。
• DO块内SELECT的强制要求：在DO块（或PL/pgSQL代码块）中直接执行SELECT时，必须为查询结果指定存储目的地（比如赋值给变量），否则就会触发“query has no destination for result data”错误。第二个例子的内层DO块直接执行SELECT *却未处理结果，因此报错；即使外层执行这个动态DO块，最终也无法返回结果。

正确实现方式 #

1. 创建返回结果集的函数 #

如果需要通过动态SQL返回多行结果，最标准的做法是创建返回表类型的PL/pgSQL函数：

CREATE OR REPLACE FUNCTION get_tbl_song()
RETURNS SETOF public.tbl_song AS $$
DECLARE
    p_sql text;
BEGIN
    p_sql := 'SELECT * FROM public.tbl_song;';
    -- 使用RETURN QUERY EXECUTE返回动态SQL的结果集
    RETURN QUERY EXECUTE p_sql;
END;
$$ LANGUAGE plpgsql;

-- 调用函数获取结果
SELECT * FROM get_tbl_song();

如果不想依赖现有表结构，也可以自定义返回列：

CREATE OR REPLACE FUNCTION get_songs()
RETURNS TABLE(song_id int, song_name text, artist text) AS $$
DECLARE
    p_sql text;
BEGIN
    p_sql := 'SELECT song_id, song_name, artist FROM public.tbl_song;';
    RETURN QUERY EXECUTE p_sql;
END;
$$ LANGUAGE plpgsql;

2. 临时执行动态SQL（无需函数） #

如果只是在psql客户端临时执行动态SQL查看结果，可直接用\gexec命令：

-- 生成SQL语句后执行
SELECT 'SELECT * FROM public.tbl_song;' AS dynamic_sql \gexec

或直接执行动态SQL（无需包裹在DO块里）：

EXECUTE 'SELECT * FROM public.tbl_song;'

注：这种方式仅适用于交互式客户端（如psql）或支持直接执行动态SQL的应用驱动。

3. 代码块内处理多行结果 #

如果仅需在PL/pgSQL代码块内部处理多行数据，而非返回给外部，可使用FOR循环遍历结果：

DO $$
DECLARE
    p_sql text;
    song_record public.tbl_song%ROWTYPE;
BEGIN
    p_sql := 'SELECT * FROM public.tbl_song;';
    FOR song_record IN EXECUTE p_sql LOOP
        -- 对每行数据进行处理，比如打印
        RAISE NOTICE 'Song: %', song_record.song_name;
    END LOOP;
END $$;

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