PostgreSQL函数：如何在函数内用新建表返回输出？

语法错误原因 #

你遇到的ERROR: syntax error at or near "CREATE"是因为：

• RETURN QUERY()只能包裹单个SELECT查询语句，不能在其中混合CREATE TABLE、INSERT、TRUNCATE这类DDL/DML语句。
• CTE（WITH子句）的语法要求后面必须直接跟SELECT语句，不能接CREATE TABLE这类操作。

最优解决方案：无需中间表，直接转换输出格式 #

实际上完全不需要创建临时表或常规表，直接通过UNION ALL将CTE中的列转换为键值对形式的行输出，既高效又避免了表管理的麻烦。修正后的函数如下：

CREATE OR REPLACE FUNCTION public.customer_activity(i_client_id integer, left_boundary date, right_boundary date)
RETURNS TABLE (metric_name text, metric_value text)
LANGUAGE plpgsql
AS $$
BEGIN 
    RETURN QUERY
        WITH cte_activity AS (
            SELECT 
                INITCAP(c.first_name || ' ' || c.last_name) || ', ' || lower(c.email) AS customer_info, 
                COUNT(f.film_id)::text AS num_films_rented,
                string_agg(DISTINCT INITCAP(f.title), ', ') AS rented_films_titles, 
                COUNT(p.payment_date)::text AS num_payments,
                SUM(p.amount)::text AS payments_amount
            FROM public.customer c 
            JOIN rental r ON r.customer_id = c.customer_id 
            JOIN inventory i ON r.inventory_id = i.inventory_id 
            JOIN film f ON f.film_id = i.film_id
            JOIN payment p ON p.rental_id = r.rental_id 
            WHERE r.rental_date BETWEEN left_boundary AND right_boundary 
              AND c.customer_id = i_client_id
            GROUP BY c.customer_id, customer_info
        )
        SELECT 'customer''s info'::text AS metric_name, customer_info AS metric_value FROM cte_activity
        UNION ALL
        SELECT 'num. of films rented'::text, num_films_rented FROM cte_activity
        UNION ALL
        SELECT 'rented films'' titles'::text, rented_films_titles FROM cte_activity
        UNION ALL
        SELECT 'num. of payments'::text, num_payments FROM cte_activity
        UNION ALL
        SELECT 'payments'' amount'::text, payments_amount FROM cte_activity;
END;
$$;

关键修改点说明 #

• 去掉了不必要的中间表操作，直接通过UNION ALL将CTE的每一列转为一行输出。
• 将数值类型（COUNT、SUM的结果）转为text类型，统一匹配返回表的metric_value字段类型（原代码用CHAR(500)不够灵活，改用text更合适）。
• 简化了列名（去掉带空格和单引号的标识符，避免SQL语法歧义）。

关于中间表与TRUNCATE的疑问 #

1. 为什么不建议用常规表：如果每次调用函数都创建public.output_table，第二次调用就会报错“表已存在”；如果先DROP再CREATE，并发调用时会出现冲突，而且会污染公共schema，完全没必要。
2. 临时表是否可行：临时表的作用域仅限于当前会话，会话结束后自动删除，不需要TRUNCATE。但即使使用临时表，也需要分开执行CREATE、INSERT、SELECT语句，不能放在RETURN QUERY()里，示例如下：

-- 临时表方案示例（不推荐，不如直接转换高效）
CREATE OR REPLACE FUNCTION public.customer_activity(i_client_id integer, left_boundary date, right_boundary date)
RETURNS TABLE (metric_name text, metric_value text)
LANGUAGE plpgsql
AS $$
BEGIN 
    -- 创建临时表
    CREATE TEMP TABLE IF NOT EXISTS temp_output (metric_name text, metric_value text);
    TRUNCATE temp_output; -- 清空之前的数据（如果会话内重复调用）

    -- 插入数据
    WITH cte_activity AS (
        SELECT 
            INITCAP(c.first_name || ' ' || c.last_name) || ', ' || lower(c.email) AS customer_info, 
            COUNT(f.film_id)::text AS num_films_rented,
            string_agg(DISTINCT INITCAP(f.title), ', ') AS rented_films_titles, 
            COUNT(p.payment_date)::text AS num_payments,
            SUM(p.amount)::text AS payments_amount
        FROM public.customer c 
        JOIN rental r ON r.customer_id = c.customer_id 
        JOIN inventory i ON r.inventory_id = i.inventory_id 
        JOIN film f ON f.film_id = i.film_id
        JOIN payment p ON p.rental_id = r.rental_id 
        WHERE r.rental_date BETWEEN left_boundary AND right_boundary 
          AND c.customer_id = i_client_id
        GROUP BY c.customer_id, customer_info
    )
    INSERT INTO temp_output (metric_name, metric_value)
    VALUES 
        ('customer''s info', (SELECT customer_info FROM cte_activity)),
        ('num. of films rented', (SELECT num_films_rented FROM cte_activity)),
        ('rented films'' titles', (SELECT rented_films_titles FROM cte_activity)),
        ('num. of payments', (SELECT num_payments FROM cte_activity)),
        ('payments'' amount', (SELECT payments_amount FROM cte_activity));

    -- 返回结果
    RETURN QUERY SELECT * FROM temp_output;
END;
$$;

但这个方案比直接转换输出的方式多了表操作的开销，所以优先推荐第一种无表方案。
3. 是否需要TRUNCATE：如果用常规表，每次调用前必须TRUNCATE或DROP，但不建议这么做；如果用临时表，会话内重复调用时需要TRUNCATE（否则会累积数据），但临时表本身会话结束就消失；无表方案则完全不需要考虑这个问题。

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