Search Postgresql Archives

Re: Possible to create canonicalized range type without being superuser?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Jul 5, 2016 at 5:37 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx> 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;
 ​
David J.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux