PostgreSQL技术问询：创建N列空表及函数返回空表实现

这得看你要创建的列数是固定值还是需要动态生成，两种场景的解决方式如下：

• 固定列数的场景：直接用标准的CREATE TABLE语句定义结构就行，不需要插入任何数据，比如创建3列的空表：

CREATE TABLE empty_table (
    col1 INT,
    col2 TEXT,
    col3 BOOLEAN
);

执行后就能得到一个结构完全符合定义的空表。

• 动态生成N列的场景：如果N很大（比如几十上百列），手动写太繁琐，可以用动态SQL批量生成。比如下面这个PL/pgSQL函数，能帮你快速创建指定列数的空表，列名按col1、col2...colN命名，默认用INT类型（你可以自行修改成需要的数据类型）：

CREATE OR REPLACE FUNCTION create_empty_table_with_n_cols(table_name TEXT, n INT)
RETURNS VOID AS $$
DECLARE
    col_defs TEXT;
BEGIN
    -- 生成列定义字符串，格式如"col1 INT, col2 INT, ..."
    SELECT string_agg('col' || i || ' INT', ', ')
    INTO col_defs
    FROM generate_series(1, n) AS i;
    
    -- 执行动态创建表的语句
    EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (' || col_defs || ')';
END;
$$ LANGUAGE plpgsql;

使用时调用SELECT create_empty_table_with_n_cols('my_big_empty_table', 20);，就能创建一个包含20列INT类型的空表。

你原来的写法遇到的核心问题是：CASE只能返回单个值或单行结果，没法直接返回符合表结构的多行结果集。这里给你两种实用的解决方式：

方法1：用PL/pgSQL条件判断（最直观） #

如果你的函数是用PL/pgSQL编写的，直接通过IF EXISTS判断CTE是否有数据，再分别返回对应结果即可。比如假设函数要返回(id INT, username TEXT)结构的表：

CREATE OR REPLACE FUNCTION get_user_data()
RETURNS TABLE(id INT, username TEXT) AS $$
BEGIN
    WITH mycte AS (
        -- 这里是你的CTE查询逻辑，比如从用户表筛选特定数据
        SELECT id, username FROM users WHERE signup_date > '2024-01-01'
    )
    -- 判断CTE是否存在数据
    IF EXISTS (SELECT 1 FROM mycte) THEN
        -- 有数据时返回CTE的查询结果
        RETURN QUERY SELECT id, username FROM mycte;
    ELSE
        -- 无数据时返回空表：WHERE 1=0确保没有行返回，同时结构与函数定义匹配
        RETURN QUERY SELECT NULL::INT, NULL::TEXT WHERE 1=0;
    END IF;
END;
$$ LANGUAGE plpgsql;

方法2：纯SQL方式（适合不想用PL/pgSQL的场景） #

可以用UNION ALL结合EXISTS条件，将两种情况的结果合并，确保只有符合条件的部分会被返回：

CREATE OR REPLACE FUNCTION get_user_data()
RETURNS TABLE(id INT, username TEXT) AS $$
WITH mycte AS (
    SELECT id, username FROM users WHERE signup_date > '2024-01-01'
)
SELECT id, username FROM mycte
-- 当CTE有数据时返回这部分结果
WHERE EXISTS (SELECT 1 FROM mycte)
UNION ALL
-- 当CTE无数据时返回空结果（WHERE 1=0保证不会返回任何行）
SELECT NULL::INT, NULL::TEXT WHERE NOT EXISTS (SELECT 1 FROM mycte) AND 1=0;
$$ LANGUAGE sql;

这种写法里，后半段的AND 1=0确保即使NOT EXISTS条件成立，也不会返回任何行，完美实现空表返回的需求。

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