Postgres DOMAIN and CHECK for LDH domain names.

dns.sql

-- Postgres DOMAIN and CHECK for LDH domain names.

-- Derived from RFCs 1035 and 1123.
CREATE EXTENSION IF NOT EXISTS citext;
CREATE DOMAIN dns AS citext
DEFAULT '.'
CHECK ( VALUE ~ '(?xi)
                 ^(    [a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?
                   ([.][a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?){0,126})?
                  [.]?$'
        AND length(trim(TRAILING '.' FROM VALUE)) <= 253 );

-- Domain normalization for indexes and comparison. Removes dot unless name is
-- empty and converts to lowercase.
CREATE FUNCTION dns_normalize(dns) RETURNS dns AS $$
DECLARE
  trimmed dns := lower(trim(TRAILING '.' FROM $1));
BEGIN
  IF trimmed = '' THEN RETURN '.';
                  ELSE RETURN trimmed;
  END IF;
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT;