如何在PostgreSQL中通过函数传入的JSON参数创建自定义列的表

错误原因分析 #

你遇到的function string_agg(text, unknown, unknown) does not exist错误，是因为**string_agg函数仅接受两个参数**：要聚合的表达式和分隔符，而你的代码里传入了三个参数（string_agg(..., '', '')）。此外，JSON解析逻辑存在问题：传入的metadata是包含columns数组的对象，直接用json_array_elements(metadata)无法正确提取列定义数组。

修正后的函数实现 #

以下是符合需求的正确函数，同时优化了JSON解析、约束判断和SQL安全防护：

CREATE OR REPLACE FUNCTION create_table(table_name text, metadata jsonb) 
RETURNS VOID AS
$$
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I (%s);',
        table_name,
        (
            SELECT string_agg(
                format(
                    '%I %s %s',
                    col ->> 'name',
                    col ->> 'dataType',
                    CASE WHEN (col ->> 'isRequired')::boolean THEN 'NOT NULL' ELSE 'NULL' END
                ),
                ', '
            )
            FROM jsonb_array_elements(metadata -> 'columns') AS col
        )
    );
END
$$
LANGUAGE plpgsql VOLATILE;

关键优化点 #

• 使用%I占位符处理表名和列名，避免SQL注入风险，同时支持包含特殊字符的名称
• 通过metadata -> 'columns'正确提取列定义数组，再用jsonb_array_elements解析
• 将isRequired字段转换为布尔类型判断，比字符串匹配更可靠
• string_agg使用, 作为分隔符，生成符合语法的列定义列表

测试示例 #

调用函数时传入表名和示例JSONB参数：

SELECT create_table(
    'type_1',
    '{
        "columns": [
            {"name": "name", "dataType": "Text", "isRequired": true},
            {"name": "type", "dataType": "Text", "isRequired": false},
            {"name": "id", "dataType": "Text", "isRequired": true}
        ]
    }'::jsonb
);

执行后会生成你期望的SQL：

CREATE TABLE IF NOT EXISTS type_1 (
    name TEXT NOT NULL,
    type TEXT NULL,
    id TEXT NOT NULL
);

补充说明 #

如果需要继续使用json类型参数，只需将函数参数类型改为json，并把jsonb_array_elements替换为json_array_elements即可。

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