Paul Jungwirth <pj@illuminatedcomputing.com> writes: > The problem is this (tried on 9.3 and 9.5): > db=> create type inetrange; > ERROR: must be superuser to create a base type > So I'm wondering whether there is any way around this circle without > being a superuser?
The only other obvious way to deal with this is to allow the canonical function to be defined after the range type is created, and then added to the type via an ALTER TYPE command. But then you have an interval where the type is not restricted, in which you might store values that aren't canonical.
Can the canonical function be definitionally optional but runtime required? That is, have it only be an error to use a type lacking a canonical function? If so I think a usable idiom is that for types that don't want to canonicalize (i.e., presently have a NULL assigned) they would make an explicit declaration by doing something like:
CREATE TYPE int4range AS RANGE (subtype = int4, canonical = int4_identity);
Now you have a window where the type is incompletely defined and when the missing canonical function is encountered the system balks. At some future point a function can be associated via ALTER TYPE which makes the type completely defined.
CREATE TYPE inetrange AS RANGE (subtype = inet, canonical = NULL);
ALTER TYPE inetrange SET PROPERTY canonical = inet_canonicalizer;
