PostgreSQL中使用EXECUTE执行交叉表查询无输出问题求助

问题场景 #

直接执行以下crosstab交叉表查询时，能得到正确结果：

SELECT * FROM crosstab(
        $q$
        SELECT ep, monitoring ->> 'type' AS type, (monitoring ->> 'status') AS status
        FROM job_monitoring
        WHERE monitoring ? 'type'
        ORDER BY ep, type
        $q$
    ) AS ct (ep VARCHAR, "CDC" TEXT, "DI" TEXT)

查询结果：

但将查询存入变量并通过EXECUTE在DO块中执行时，没有任何输出：

DO $$
DECLARE
    query TEXT;
BEGIN
query :='SELECT * FROM crosstab(
            $q$
            SELECT ep, monitoring ->> ''type'' AS type, (monitoring ->> ''status'') AS status
            FROM job_monitoring
            WHERE monitoring ? ''type''
            ORDER BY ep, type
            $q$
        ) AS ct (ep VARCHAR, "CDC" TEXT, "DI" TEXT)';
        RAISE NOTICE '%', query;
execute query;
END $$;

问题原因 #

PostgreSQL的DO语句是用于执行无返回值的过程式代码的，它不会将内部执行的SELECT查询结果返回给客户端。哪怕你在DO块里执行了查询，结果也只会在数据库内部处理，不会输出到终端或客户端工具中。

解决方案 #

根据你的需求，有两种可行的处理方式：

1. 在DO块中输出结果（仅用于测试/查看） #

通过遍历查询结果，用RAISE NOTICE将每条记录输出到日志或客户端：

DO $$
DECLARE
    query TEXT;
    rec RECORD;
BEGIN
query :='SELECT * FROM crosstab(
            $q$
            SELECT ep, monitoring ->> ''type'' AS type, (monitoring ->> ''status'') AS status
            FROM job_monitoring
            WHERE monitoring ? ''type''
            ORDER BY ep, type
            $q$
        ) AS ct (ep VARCHAR, "CDC" TEXT, "DI" TEXT)';
    -- 遍历结果并输出
    FOR rec IN EXECUTE query LOOP
        RAISE NOTICE 'ep: %, CDC: %, DI: %', rec.ep, rec."CDC", rec."DI";
    END LOOP;
END $$;

2. 创建函数返回结果（适合业务场景） #

如果需要将动态查询的结果返回给客户端，必须使用返回表类型的函数，而不是DO块：

CREATE OR REPLACE FUNCTION get_crosstab_result()
RETURNS TABLE(ep VARCHAR, "CDC" TEXT, "DI" TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    query TEXT;
BEGIN
    query :='SELECT * FROM crosstab(
                $q$
                SELECT ep, monitoring ->> ''type'' AS type, (monitoring ->> ''status'') AS status
                FROM job_monitoring
                WHERE monitoring ? ''type''
                ORDER BY ep, type
                $q$
            ) AS ct (ep VARCHAR, "CDC" TEXT, "DI" TEXT)';
    RETURN QUERY EXECUTE query;
END $$;

调用函数获取结果：

SELECT * FROM get_crosstab_result();

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