Count nulls

Library Snippets

Count Nulls

Works with PostgreSQL

>=8.3

Written in

C

Depends on

Nothing

Author: Alvaro Herrera, sponsored by Enova Financial

Comment by: Alexey Klyukin

This is a simple function that counts a number of NULLs among its arguments.

#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);
}

The relevant SQL definition (for 8.4 and above):

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

If you are on 8.3 and below you have to declare each N-argument function separately due to a lack of VARIADIC:

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';

One should build it with a fairly standard Makefile:

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

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

A simple usage example, let's install a constraint on a table that would check that exactly one table column is NULL in every row.

# 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