sql - 更新以特定字符串开头的多个列

Question

我正在尝试更新数据库中的一堆列以测试功能。我有一个用 hibernate 构建的表，因此为嵌入式实体创建的所有列都以相同的名称开头。 IE。 contact_info_address_street1、contact_info_address_street2等

我想弄清楚是否有办法做一些影响:

UPDATE table SET contact_info_address_* = null;

如果没有，我知道我可以做很长的路，只是寻找一种方法来帮助自己在未来需要为一组不同的列重新做这件事。

Answer 1

为此您需要动态 SQL。因此，您必须防御可能的 SQL 注入(inject)。

基本查询

生成所需 DML 命令的基本查询如下所示:

SELECT format('UPDATE tbl SET (%s) = (%s)'
               ,string_agg (quote_ident(attname), ', ')
               ,string_agg ('NULL', ', ')
             )
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    NOT attisdropped 
AND    attnum > 0
AND    attname ~~ 'foo_%';

返回:

UPDATE tbl SET (foo_a, foo_b, foo_c) = (NULL, NULL, NULL);

利用 "column-list syntax" of UPDATE缩短代码并简化任务。

我查询system catalogs而不是 information schema因为后者虽然是标准化的并保证可以跨主要版本移植，但也是出了名的慢，有时甚至笨拙。有利有弊，参见:

• Get column names and data types of a query, table or view

列名的

quote_ident() 可防止 SQL 注入(inject) - 对于标识符也是必需的。

string_agg()需要 9.0+。

使用 PL/pgSQL 函数实现完全自动化

CREATE OR REPLACE FUNCTION f_update_cols(_tbl regclass, _col_pattern text
                                       , OUT row_ct int, OUT col_ct bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql, col_ct
          format('UPDATE tbl SET (%s) = (%s)'
               , string_agg (quote_ident(attname), ', ')
               , string_agg ('NULL', ', ')
                )
        , count(*)
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped            -- no dropped columns
   AND    attnum > 0                  -- no system columns
   AND    attname LIKE _col_pattern;  -- only columns matching pattern

   -- RAISE NOTICE '%', _sql;         -- output SQL for debugging
   EXECUTE _sql;

   GET DIAGNOSTICS row_ct = ROW_COUNT;
END
$func$;

COMMENT ON FUNCTION f_update_cols(regclass, text)
IS 'Updates all columns of table _tbl ($1)
that match _col_pattern ($2) in a LIKE expression.
Returns the count of columns (col_ct) and rows (row_ct) affected.';

调用:

SELECT * FROM f_update_cols('myschema.tbl', 'foo%');

为了使函数更实用，它返回注释中描述的信息。更多关于 obtaining the result status在手册中的 plpgsql 中。

我使用变量 _sql 来保存查询字符串，因此我可以收集同一查询中找到的列数 (col_ct)。

对象标识符类型 regclass 也是自动避免表名的 SQL 注入(inject)(并清理非标准名称)的最有效方法。您可以使用schema-qualified table names 来避免歧义。如果您(可以)在您的数据库中有多个模式，我建议您这样做!见:

• Table name as a PostgreSQL function parameter

db<> fiddle here
<子>旧sqlfiddle