由於 plpgsql 中字元串周圍不需要括號，動態更新失敗

在 Mac OSX 10.9.2 上使用 PostgreSQL 9.3.4 和 Postgres.app。

我想將一個函式應用於表中的所有列，特別是我想要trim空白。到目前為止，我已經在一個文件中得到了這個test.sql：（編輯。在下面的答案後修復了程式碼）

do $$
declare
target text;
begin
   for target in
       select quote_ident(column_name)
       from information_schema.columns
       where table_name = 'cds' and data_type = 'character varying'
   loop
       RAISE NOTICE 'Calling trim(%)', target;
       execute 'update cds set '
           || quote_ident(target)
           || ' = trim('
           || quote_ident(target)
           || ')';
   end loop;
end
$$;

這select給了我一個很好的表中所有列名的列表，我希望for循環遍歷所有這些：

gakera=# select column_name from information_schema.columns
        where table_name = 'cds' and data_type = 'character varying';

column_name 
-------------
userID
junk1
dates
times
junk2
junk3
servID
junk4
junk5

側軌

不是問題，只是額外的混亂。

樂趣從\i ~/path/test.sql這個錯誤開始：

psql:/path/test.sql:18: NOTICE:  Calling trim((anum))
psql:/path/test.sql:18: ERROR:  function quote_ident(record) does not exist
LINE 2:    || quote_ident(target)
             ^
HINT:  No function matches the given name and argument types.
      You might need to add explicit type casts.

這是怎麼回事quote_ident？我要嘗試進行完整性檢查，我從http://www.postgresql.org/docs/9.3/static/functions-string.html複製了這個

gakera=# select quote_ident('test');
quote_ident 
-------------
test

好的，然後進行瘋狂檢查：

gakera=# select quote_indeed('test');
ERROR:  function quote_indeed(unknown) does not exist
LINE 1: select quote_indeed('test');
              ^
HINT:  No function matches the given name and argument types.
      You might need to add explicit type casts.

呃，無論如何，我會quote_ident從有問題的行中刪除該功能（它沒有抱怨這select quote_ident部分，所以我保留了它。現在我有：

   execute 'update cds set '
       || target
       || ' = trim('
       || target
       || ')';

題

這是事情開始變得有趣的地方。當我在得到之前執行它時：

psql:/path/test.sql:18: NOTICE:  Calling trim((anum))
psql:/path/test.sql:18: ERROR:  syntax error at or near "trim"
LINE 1: update cds set (anum) = trim((anum))
                                ^
QUERY:  update cds set (anum) = trim((anum))
CONTEXT:  PL/pgSQL function inline_code_block line 11 at EXECUTE statement

為什麼要添加()周圍anum？我怎樣才能擺脫它們？該EXECUTE聲明應改為update cds set anum = trim(anum)

第一個列出的函式幾乎是正確的。變數目標應該是類型text。嘗試這個：

do $$
declare
   target text;
begin
   for target in
       select column_name
       from information_schema.columns
       where table_name = 'cds' and data_type = 'character varying'
   loop
       RAISE NOTICE 'Calling trim(%)', target;
       execute 'update cds set '
           || target
           || ' = trim('
           || target
           || ')';
   end loop;
end
$$;

@klin已經解決了您一直在詢問的錯誤。我將介紹您的一般方法。

您的DO語句將為每一列單獨執行UPDATE- 這幾乎是一次更新n列的**n倍。 由於PostgreSQL 的 MVCC 模型，這也會在表中產生n 個死元組，導致過度的表膨脹和大量的.
VACUUM

更糟糕的是，即使沒有任何變化，它也會每行更新 n 次。

我建議一種更有效的方法：

• 每行更新一次。
• 僅在有任何實際變化時才更新。

CREATE OR REPLACE FUNCTION f_trim_cols(IN _tbl regclass, OUT row_ct integer
                                                      , OUT col_ct integer)
 RETURNS record AS
$func$
DECLARE
  _sql text;
BEGIN
  SELECT format('
     UPDATE %s SET (%2$s) = (trim(%3$s))
     WHERE (%2$s) IS DISTINCT FROM (trim(%3$s))' -- test if anything changes
               , _tbl
               , string_agg (quote_ident(attname), ', ')
               , string_agg (quote_ident(attname), '), trim(')
               )
       , count(*)::int  -- count columns
  INTO   _sql, col_ct
  FROM   pg_attribute
  WHERE  attrelid = _tbl
  AND    atttypid IN ('text'::regtype, 'varchar'::regtype) -- basic string types
  AND    NOT attisdropped 
  AND    attnum > 0;

  -- RAISE NOTICE '%', _sql;  --  debug
  EXECUTE _sql;  GET DIAGNOSTICS row_ct = ROW_COUNT;
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_trim_cols(regclass) IS 'Trim all columns of table _tbl ($1).
Return count of applicable columns (col_ct) and rows actually changed (row_ct) .
Call:
  SELECT * FROM f_trim_cols($$city$$)   --';

生成如下 SQL 程式碼：

UPDATE city SET (city, country) = (trim(city), trim(country))
WHERE (city, country) IS DISTINCT FROM (trim(city), trim(country));

我也定位text以及varchar列。

一切都經過消毒並且可以安全地防止 SQL 注入。

有關 SO 的相關答案中的更多解釋、連結和小提琴：

• 更新以特定字元串開頭的多個列

引用自：https://dba.stackexchange.com/questions/65221