主页 / dba / 问题 / 102957
Accepted
Fabrizio Mazzoni
Asked: 2015-06-02 07:24:32 +0800 CST

PostgreSQL 强制所有数据大写

  • 772

有没有办法将所有文本数据强制为大写，而无需重复为每个表编写函数或在客户端执行它？

postgresql postgresql-9.4

3 个回答

  1. Best Answer

    强制正确的值是一回事。每列
    的简单CHECK约束 可以可靠地完成这项工作：

    CREATE TABLE foo
   foo_id serial PRIMARY KEY
 , text_column text CHECK (upper(text_column) = text_column)
 , ...
);

    自动更正所有输入是另一回事，并不那么简单。
    它可以通过使用触发器函数来完成，该触发器函数在系统目录中动态查找列名和数据类型并将所有字符数据转换为大写。足够安全，但没有那么快和防弹。触发器可以被其他触发器规避或抵消。

    一个通用的触发功能：

    CREATE OR REPLACE FUNCTION trg_all_upper()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   -- basic character types, possibly add citext, domains or custom types
   _typ   CONSTANT regtype[] := '{text, bpchar, varchar}';
   _sql   text;
   _found bool;
BEGIN
   SELECT INTO _sql, _found
          'SELECT ' || string_agg(
              CASE WHEN a.atttypid = ANY(_typ)
              THEN format ('upper(%1$s)::%2$s AS %1$s'
                         , a.col, a.atttypid::regtype)
              ELSE col END
            , ', ') || ' FROM (SELECT ($1).*) t'
        , bool_or(a.atttypid = ANY(_typ))
   FROM  (
      SELECT a.atttypid, quote_ident(attname) AS col
      FROM   pg_attribute a
      WHERE  a.attrelid = TG_RELID  -- object ID of table that fired trigger 
      AND    a.attnum >= 1          -- exclude tableoid & friends
      AND    NOT a.attisdropped     -- exclude dropped columns
      ORDER  BY a.attnum
      ) a;

   -- RAISE NOTICE '%', _sql;  -- debug
   IF _found THEN
      EXECUTE _sql USING NEW INTO NEW;
   END IF;

   RETURN NEW;
END
$func$;

    每个表一个触发器 ：

    CREATE TRIGGER all_upper_bef_insupd
BEFORE INSERT OR UPDATE ON big
FOR EACH ROW EXECUTE PROCEDURE trg_all_upper();

    在 Postgres 11 或更高版本中使用更合理的语法：

    ...
FOR EACH ROW EXECUTE FUNCTION trg_all_upper();

    将列中具有定义的字符数据类型的 所有_typ值转换为大写。
    在 Postgres 9.4 中测试。

    有关的：

    • 21

  2. SQL-99 DOMAIN 可能是解决这个问题的好方法。

    在 PostgreSQL 中创建一个域，其中包含您的大写要求和其他要求的约束：

    CREATE DOMAIN uctext AS TEXT
  CONSTRAINT uctext_uppercase CHECK (UPPER(VALUE) = VALUE)
  CONSTRAINT uctext_not_emtpy CHECK (LENGTH(VALUE) > 0);

    然后在需要时使用您的域定义列：

    CREATE TABLE Products (
  product_code uctext NOT NULL UNIQUE,
  ...
);

    数据库将不费吹灰之力地为这些列强制使用大写。但是，在上述情况下，您必须记住 UNIQUE 索引和一般的列仅包含大写数据。因此，应用程序负责将用于匹配的所有比较数据大写。例如，以下情况可能会导致您未来的自己或您的继任者想知道为什么产品在删除后仍然存在：

    DELETE FROM Products WHERE product_code = 'valid-code-123';

    相反，您需要执行以下操作：

    DELETE FROM Products WHERE product_code = UPPER('valid-code-123');

    或者创建一个 DB API（函数、过程、触发器等）来处理大写。此外，除非先将数据转换为大写，否则所有插入都将失败。

    • 3

  3. 很抱歉回复晚了，因为我对这个网站很陌生。

    我喜欢上面的答案。然而，我使用了简单而肮脏的方式，因为我有更少的表格和我们需要的几列大写，用于显示给最终用户。

    通常，您需要大写来向最终用户显示数据。如果是这样，

    第 1 步：识别表和列并使用下面的 SQL（这是用于 POSTGRES）：

    update TABLE1 set COLUMN_NAME = Upper(COLUMN_NAME);
update TABLE2 set COLUMN_NAME = Upper(COLUMN_NAME);
update TABLE3 set COLUMN_NAME = Upper(COLUMN_NAME);
so on...

    第 2 步：使用 CRONJOB 或类似工具自动执行上述脚本（可能每 30 分钟或 1 小时甚至每分钟）

    顺便说一句，TRIGGERS（用于更新）检查约束（用于强制）可能是 REAL-TIME 的最佳解决方案，正如上面其他朋友所解释的，但您需要为每一列定义它们。

    • 0

相关问题