PLpgSQL函数在给定表中查找仅具有NULL值的列 [英] PLpgSQL function to find columns with only NULL values in a given table

问题描述

我们必须找到只有NULL值的表的列.我们正在尝试构建一个plpgsql函数，该函数采用表的名称并返回此类列的列表.

We have to find columns of a table with only NULL values. We are trying to build a plpgsql function that takes a table's name and returns the list of such columns.

如何创建这样的功能?

我们正在使用PgAdmin 1.16.

We are using PgAdmin 1.16.

推荐答案

您可以查询目录表

You can query the catalog table pg_attribute to get a list of columns which are not defined NOT NULL and therefore can hold NULL values:

SELECT quote_ident(attname) AS column_can_be_null
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass -- valid, visible table name 
AND    attnum >= 1                -- exclude tableoid & friends
AND    NOT attisdropped           -- exclude dropped columns
AND    NOT attnotnull             -- exclude columns defined NOT NULL!
ORDER  BY attnum;

tbl是您的表名(可选为模式限定).

Where tbl is your (optionally schema-qualified) table name.

没有说该列中有任何实际的NULL值.您必须测试每列.像这样:

Doesn't say there are any actual NULL values in the column. You'd have to test each column. Like this:

CREATE OR REPLACE FUNCTION f_all_null_columns_of_tbl(_tbl regclass)
  RETURNS SETOF text AS
$func$
DECLARE
   _row_ct  bigint;        -- count rows in table $1
   _sql     text;          -- SQL string to test for NULL values
   _cols    text[];        -- array of candidate column names
   _nulls   bool[];        -- array of test results
BEGIN

EXECUTE 'SELECT count(*) FROM ' || _tbl
INTO _row_ct;

IF _row_ct = 0 THEN
   RAISE EXCEPTION 'Table % has no rows!', _tbl;  -- pointless for empty table
ELSE
   RAISE NOTICE '% rows in table %.', _row_ct, _tbl; 
END IF;

SELECT INTO _sql, _cols
      'SELECT ARRAY[' || string_agg('bool_and(' || col || ' IS NULL)', ', ')
       || '] FROM ' || _tbl
    , array_agg(col)
FROM  (
   SELECT quote_ident(attname) AS col
   FROM   pg_attribute
   WHERE  attrelid = _tbl            -- valid, visible table name 
   AND    attnum >= 1                -- exclude tableoid & friends
   AND    NOT attisdropped           -- exclude dropped columns
   AND    NOT attnotnull             -- exclude columns defined NOT NULL!
   ORDER  BY attnum
   ) sub;

EXECUTE _sql INTO _nulls;

FOR i IN 1 .. array_upper(_cols, 1)
LOOP
   IF _nulls[i] THEN                 -- column is NULL in all rows
      RETURN NEXT _cols[i];
   END IF;
END LOOP;

RETURN;
END
$func$ LANGUAGE plpgsql;

致电:

SELECT f_all_null_columns_of_tbl('my_schema.my_table');

已通过Postgres 9.1和9.3测试.
这使用了许多高级的plpgsql功能.

Tested with Postgres 9.1 and 9.3.
This uses a number of advanced plpgsql features.

SQL提琴.

使用现代语法构建并执行SQL代码的相关答案:

Related answer building SQL code and executing it, with modern syntax:

• 用空值替换空字符串

关于遍历记录:

• 在RECORD列中循环

这篇关于PLpgSQL函数在给定表中查找仅具有NULL值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！