Count nulls
From PostgreSQL wiki
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
