统计空值

库代码片段

统计空值

适用于 PostgreSQL

>=8.3

用

C

依赖于

无

作者: Alvaro Herrera，由 Enova Financial 赞助

评论者: Alexey Klyukin

这是一个简单的函数，用于统计其参数中 NULL 的数量。

#include "postgres.h"

#include "fmgr.h"

PG_MODULE_MAGIC;

Datum count_nulls(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(count_nulls);
Datum
count_nulls(PG_FUNCTION_ARGS)
{
	int		nargs = PG_NARGS();
	int		num_nulls = 0;
	int		i;

	for (i = 0; i < nargs; i++)
	{
		if (PG_ARGISNULL(i))
			num_nulls++;
	}

	PG_RETURN_INT32(num_nulls);
}

相关的 SQL 定义（适用于 8.4 及更高版本）

create or replace function countnulls(VARIADIC "any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';

如果您使用的是 8.3 及更早版本，则由于缺少 VARIADIC，您必须分别声明每个 N 参数函数

create function countnulls("any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';
create function countnulls("any", "any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';	
create function countnulls("any", "any", "any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';
create function countnulls("any", "any", "any", "any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';
create function countnulls("any", "any", "any", "any", "any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';
create function countnulls("any", "any", "any", "any", "any", "any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';
create function countnulls("any", "any", "any", "any", "any", "any", "any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';
create function countnulls("any", "any", "any", "any", "any", "any", "any", "any") returns int language 'c' IMMUTABLE as 'countnulls', 'count_nulls';

应该使用相当标准的 Makefile 构建它

MODULE_big = countnulls
OBJS = count_nulls.o
DATA = countnulls.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

一个简单的使用示例，让我们在表上安装一个约束，该约束将检查每一行中只有一个表列为 NULL。

# CREATE TABLE foo(bar int, baz text, check(countnulls(bar,baz) = 1));
CREATE TABLE
# INSERT INTO foo VALUES(1,2);
ERROR:  new row for relation "foo" violates check constraint "foo_check"
# INSERT INTO foo VALUES(NULL, NULL);
ERROR:  new row for relation "foo" violates check constraint "foo_check"
# INSERT INTO foo VALUES(NULL, 1);
INSERT 0 1