Count nulls

From PostgreSQL wiki

Revision as of 14:42, 1 March 2011 by Alexk (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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
Personal tools