檢查字元類型列中是否存在空字元串

我有一個應用程序（作為其邏輯的一部分）在插入數據庫之前修剪字元串並用 NULL 替換空字元串。我想確保執行此操作的一種方法是在每個具有VARCHAR, TEXT（或類似）列的表上編寫一個 CHECK 。

假設一個人不能或不想這樣做，有一種方法可以編寫一個簡單的通用 SQL 查詢（從數據庫的元數據中獲取表和列名），以檢查數據庫中的任何文本列是否包含空字元串?

單表功能

返回給定表的所有字元類型列，其中包含空值計數 ( '') 以及它們是否已定義NOT NULL。

CREATE OR REPLACE FUNCTION f_tbl_empty_status(_tbl regclass)
 RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool)
 LANGUAGE plpgsql AS
$func$
DECLARE
  _typ      CONSTANT regtype[] := '{text, bpchar, varchar}';  -- base char types
  _sql      text;
  _col_arr  text[];
  _null_arr bool[];
BEGIN
  -- Build command
  SELECT INTO _col_arr, _null_arr, _sql
         array_agg(s.col)
       , array_agg(s.attnotnull)
       , '
  SELECT $1
       , unnest($2)
       , unnest(ARRAY [count('
                || string_agg(s.col, ' = '''' OR NULL), count(')
                                  || ' = '''' OR NULL)])
       , unnest($3)
  FROM   ' || _tbl
  FROM  (
     SELECT quote_ident(attname) AS col, attnotnull
     FROM   pg_attribute
     WHERE  attrelid = _tbl           -- valid, visible, legal table name 
     AND    attnum >= 1               -- exclude tableoid & friends
     AND    NOT attisdropped          -- exclude dropped columns
  -- AND    NOT attnotnull            -- include columns defined NOT NULL
     AND    atttypid = ANY(_typ)      -- only character types
     ORDER  BY attnum
     ) AS s;

  -- Debug
  -- RAISE NOTICE '%', _sql;

  -- Execute
  IF _sql IS NULL THEN
     -- do nothing, nothing to return
  ELSE
     RETURN QUERY EXECUTE _sql
     USING  _tbl::text, _col_arr, _null_arr;
  END IF;
END
$func$;

稱呼：

SELECT * FROM f_tbl_empty_status('tbl_name'); -- optionally schema-qualified

回報：

tbl   | col        | empty_ct | not_null
------+------------+----------+---------
tbl1  | txt        | 0        | f
tbl1  | vc         | 3        | f
tbl1  | "oDD name" | 7        | f

適用於Postgres 9.1或更高版本。

根據目前的search_path.

如果需要，輸出表名和列名會自動轉義。

empty_ct是列的值為空字元串的行數

not_null報告列是否已定義NOT NULL（因此您不能將可能的空字元串轉換為 NULL！）

輸入表名可以選擇是模式限定的，否則預設為目前的search_path.

您的角色需要權限才能實際從給定表中讀取。

該函式經過高度優化，僅對給定表執行一次掃描以檢查所有相關列。

應該可以安全地防止 SQL 注入。

使用並行unnest()來稍微簡化複雜的程式碼：

• 將多個數組取消嵌套到行中

您將對 SO 上的這個相關答案感興趣，以實際替換空字元串- 並提供更多解釋：

• 用空值替換空格

也匹配僅包含空格字元的字元串

正如您評論的trim(s.col, ' ') = ''那樣，這項工作做得很好。但這裡有一個捷徑：

s.col::char = ''

如何？

char是 的別名character(1)，很少使用的空白填充類型。值用空格字元填充到長度說明符的右側（1在這種情況下，但不相關）。對於這種類型，尾隨空格實際上是微不足道的。所以與or' '相同。瞧。是的，它也更快，我測試過。''``' '

要查找僅包含空格字元的字元串（而不是其他空格！），請將強制轉換添加到上面的這些行中：

                 || string_agg(s.col, '**::char** = '''' OR NULL), count(')
                                   || '**::char** = '''' OR NULL)])

用於報告整個模式的包裝函式

CREATE OR REPLACE FUNCTION f_schema_empty_status(_sch text DEFAULT 'public')
 RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool)
 LANGUAGE plpgsql AS
$func$
DECLARE 
  _tbl regclass;
BEGIN
  FOR _tbl IN
     SELECT c.oid
     FROM   pg_class c
     JOIN   pg_namespace n ON n.oid = c.relnamespace
     WHERE  n.nspname = _sch  -- 'public' by default
  -- AND    c.relname LIKE 'my_pattern%'  -- optionally filter table names
     AND    c.relkind = 'r'  -- regular tables only
     ORDER  BY relname
  LOOP
  -- Debug
  -- RAISE NOTICE 'table: %', _tbl;

     RETURN QUERY
     SELECT * FROM f_tbl_empty_status(_tbl);
  END LOOP;
END
$func$;

稱呼：

SELECT * FROM f_schema_empty_status();  -- defaults to 'public' without parameter

回報：

tbl   | col        | empty_ct | not_null
------+------------+----------+---------
tbl1  | txt        | 0        | f
tbl1  | vc         | 3        | f
tbl1  | "oDD name" | 7        | f
tbl2  | some_text  | 123      | t
...

db<>fiddle here

舊sqlfiddle

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