如何在PostgreSQL中设置列长度的全局限制？

PostgreSQL没有Oracle那样的全局触发器，但可以通过**事件触发器（Event Triggers）**实现类似的DDL校验逻辑，针对指定数据库（如foo）拦截不符合规则的表创建或修改操作。

实现步骤 #

1. 创建校验函数 #

编写PL/pgSQL函数，检查新创建/修改的表中是否存在违规列类型或超长的可变长度字符类型：

CREATE OR REPLACE FUNCTION validate_column_types()
RETURNS event_trigger AS $$
DECLARE
    rec RECORD;
    col RECORD;
BEGIN
    -- 遍历当前DDL操作涉及的对象（仅处理表）
    FOR rec IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag IN ('CREATE TABLE', 'ALTER TABLE') LOOP
        -- 跳过系统模式下的表
        IF rec.schema_name IN ('pg_catalog', 'information_schema', 'pg_toast') THEN
            CONTINUE;
        END IF;

        -- 遍历表的所有列
        FOR col IN SELECT attname, atttypid, atttypmod
                   FROM pg_attribute
                   WHERE attrelid = rec.objid AND attnum > 0 AND NOT attisdropped LOOP
            -- 禁止的类型：TEXT、JSON、JSONB、XML、BYTEA
            IF col.atttypid IN (
                (SELECT oid FROM pg_type WHERE typname = 'text'),
                (SELECT oid FROM pg_type WHERE typname = 'json'),
                (SELECT oid FROM pg_type WHERE typname = 'jsonb'),
                (SELECT oid FROM pg_type WHERE typname = 'xml'),
                (SELECT oid FROM pg_type WHERE typname = 'bytea')
            ) THEN
                RAISE EXCEPTION '表 %.% 中的列 % 使用了禁止的类型：%',
                    rec.schema_name, rec.object_name, col.attname,
                    (SELECT typname FROM pg_type WHERE oid = col.atttypid);
            END IF;

            -- 检查VARCHAR/CHARACTER VARYING的长度限制（atttypmod = 长度 + 4）
            IF col.atttypid IN (
                (SELECT oid FROM pg_type WHERE typname = 'varchar'),
                (SELECT oid FROM pg_type WHERE typname = 'bpchar') -- 可选：如需限制CHAR(n)可保留
            ) THEN
                -- atttypmod为-1表示无长度限制（即VARCHAR而非VARCHAR(n)）
                IF col.atttypmod = -1 THEN
                    RAISE EXCEPTION '表 %.% 中的列 % 使用了无长度限制的 % 类型，必须指定长度且≤500',
                        rec.schema_name, rec.object_name, col.attname,
                        (SELECT typname FROM pg_type WHERE oid = col.atttypid);
                ELSIF (col.atttypmod - 4) > 500 THEN
                    RAISE EXCEPTION '表 %.% 中的列 % 长度为 %，超过最大限制500',
                        rec.schema_name, rec.object_name, col.attname, (col.atttypmod - 4);
                END IF;
            END IF;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

2. 创建事件触发器 #

针对foo数据库，创建事件触发器绑定上述函数，拦截CREATE TABLE和ALTER TABLE操作：

-- 切换到foo数据库
\c foo

-- 创建事件触发器，触发时机为DDL命令执行后（确保能获取完整的表结构）
CREATE EVENT TRIGGER enforce_column_restrictions
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE')
EXECUTE FUNCTION validate_column_types();

关键说明 #

• 权限要求：事件触发器需要超级用户权限创建，SECURITY DEFINER确保函数以创建者权限执行，避免普通用户绕过限制。
• 系统模式过滤：函数中跳过了pg_catalog、information_schema等系统模式，仅校验用户自定义模式下的表。
• 覆盖场景：不仅拦截新建表，还处理ALTER TABLE添加/修改列的操作，防止后续违规修改。
• 测试验证：
  • 尝试创建带TEXT列的表：CREATE TABLE test (col TEXT); 会抛出异常。
  • 尝试创建VARCHAR(600)的列：CREATE TABLE test (col VARCHAR(600)); 会抛出长度超限异常。
  • 创建VARCHAR(500)的列：CREATE TABLE test (col VARCHAR(500)); 可正常执行。

补充提示 #

如果需要禁止更多类型（如CITEXT等扩展类型），只需在禁止类型的atttypid列表中添加对应类型的OID即可。若要允许CHAR(n)类型但限制长度，可调整bpchar类型的检查逻辑。

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