Possible to create canonicalized range type without being superuser?

From: Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Possible to create canonicalized range type without being superuser?
Date: 2016-07-05 20:57:37
Message-ID: 577C1F41.6020903@illuminatedcomputing.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm trying to create a custom inetrange type. This works:

CREATE TYPE inetrange AS RANGE (
subtype = inet,
subtype_diff = inet_diff
);

But since inet is discrete, not continuous, I'd like to define a
canonical function too:

CREATE TYPE inetrange AS RANGE (
subtype = inet,
subtype_diff = inet_diff,
canonical = inetrange_canonical
);

But of course I can't define the function until I have the type, and I
can't define the type until I have the function.

Now the docs address this
(https://www.postgresql.org/docs/9.5/static/sql-createtype.html) and
recommend:

> Creating a canonical function is a bit tricky, since it must be
defined before the range type can be declared. To do this, you must
first create a shell type, which is a placeholder type that has no
properties except a name and an owner. This is done by issuing the
command CREATE TYPE name, with no additional parameters. Then the
function can be declared using the shell type as argument and result,
and finally the range type can be declared using the same name. This
automatically replaces the shell type entry with a valid range type.

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?

Thanks!
Paul

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-07-05 21:37:11 Re: Possible to create canonicalized range type without being superuser?
Previous Message Paul Linehan 2016-07-05 20:17:34 Re: pg_dump fundenental question