PostgreSQL更新表结构后PL/pgSQL函数查询报错问题咨询

报错的核心原因是：dynamic_query_single_table函数的返回类型由调用时传入的NULL::my_table在查询执行前就确定了，必须严格匹配my_table的表结构。但你更新目标表csv_congregational_ministries_2023_20240109090450的结构后，它的第6列类型变为character varying(255)，而my_table的第6列还是旧的double precision，导致查询结果的结构和函数预设的返回类型不匹配。

你分开执行DROP/CREATE和函数调用能正常运行，是因为DROP/CREATE操作会更新my_table的结构，此时再调用函数，传入的NULL::my_table已经是新结构，和目标表匹配。但在函数内部，同一事务中的DROP/CREATE不会改变函数调用时已经确定的返回类型，所以仍然会触发结构不匹配错误。

办法1：拆分操作，先同步表结构再查询 #

将表结构同步和数据查询拆分为两个独立步骤，确保调用函数时my_table的结构已与目标表一致：

1. 创建同步表结构的函数：

CREATE OR REPLACE FUNCTION sync_table_structure(_table_name text, _year int)
RETURNS void AS $$
    DECLARE
        table_selected text;
    BEGIN
        table_selected := get_table(_table_name, _year);
        -- 只同步结构，不复制数据（加LIMIT 0）
        EXECUTE FORMAT('DROP TABLE IF EXISTS %s; CREATE TABLE %s AS SELECT * FROM %s LIMIT 0;', _table_name, _table_name, table_selected);
    END;
$$ LANGUAGE plpgsql;

2. 先执行同步，再调用查询函数：

SELECT sync_table_structure('my_table', 2023);
SELECT * FROM dynamic_query_single_table(NULL::my_table, 2023);

办法2：修改函数返回JSON格式，摆脱结构依赖 #

如果不需要返回严格的行类型，可以将函数改为返回JSON数据，这样无论目标表结构如何变化都能正常返回：

CREATE OR REPLACE FUNCTION dynamic_query_single_table(_table_name text, _year int)
RETURNS SETOF json AS $$
    DECLARE
        table_selected text;
    BEGIN
        table_selected := get_table(_table_name, _year);
        RETURN QUERY EXECUTE FORMAT('SELECT row_to_json(t) FROM %s t;', table_selected);
    END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT * FROM dynamic_query_single_table('my_table', 2023);

办法3：使用动态返回类型（需手动指定列结构） #

改用RETURNS SETOF record，调用时手动指定与目标表匹配的列结构：

修改函数：

CREATE OR REPLACE FUNCTION dynamic_query_single_table(_table_name text, _year int)
RETURNS SETOF record AS $$
    DECLARE
        table_selected text;
    BEGIN
        table_selected := get_table(_table_name, _year);
        RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s;', table_selected);
    END;
$$ LANGUAGE plpgsql;

调用示例（需替换为实际列名和类型）：

SELECT * FROM dynamic_query_single_table('my_table', 2023) AS (
    id integer,
    name varchar(255),
    column3 date,
    column4 boolean,
    column5 integer,
    column6 varchar(255), -- 与目标表新类型一致
    column7 text
);

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