regex - 计算行中的非空列

Question

我正在尝试确定填充的特定列的数量，这是我正在尝试做的事情:

  foo := COALESCE($1.col1, '') || ' ' ||
    COALESCE($1.col2, '') || ' ' ||
    COALESCE($1.col3, '') || ' ' ||
    COALESCE($1.col4, '');
  foo := REPLACE(foo, var, ' ');
  words := string_to_array(foo, ' ');
  RETURN array_length(words, 1);

其中 var 是空格，\s 似乎不起作用。我愿意接受任何其他查找非零列数的方法。

Answer 1

使用 JSON functions，您无需拼出所有列 - 甚至不需要知道它们 - 就可以做到这一点在 Postgres 9.3 或更高版本中:

SELECT t.*, count(value)::int AS notnull_ct   -- cast to int is optional
FROM   tbl t, json_each_text(row_to_json(t))  -- implicit LATERAL join
-- WHERE key LIKE 'col%' -- optionally consider only selected columns
GROUP  BY tbl_id;        -- PK column

• What is the difference between LATERAL and a subquery in PostgreSQL?

json_each_text() 默认返回 (key, value)。在命名冲突的情况下使用不同的别名和/或表限定名称。如果您只对选定的列感兴趣，则可以在 WHERE 子句中过滤列名。

或者使用附加模块hstore出于同样的目的，至少从 Postgres 8.3 开始可用:

• Key value pair in PostgreSQL

SELECT t.*, count(v)::int AS notnull_ct
FROM   tbl t, svals(hstore(t)) v
GROUP  BY tbl_id;

主要特点是 count() 不计算 NULL 值(也从不返回 NULL)。正是您所需要的。

您可以将其封装在一个函数中。具有多态输入类型的简单 SQL 函数可以完成这项工作:

CREATE OR REPLACE FUNCTION f_count_notnull_in_row(ANYELEMENT)
  RETURNS int LANGUAGE sql IMMUTABLE AS
'SELECT count(value)::int
 FROM   json_each_text(row_to_json($1))';

调用:

SELECT *, f_count_notnull_in_row(t)
FROM  tbl t;

SQL Fiddle (重用 Bill 的设置)。