Stated differently, is there a simple way to extend or "subclass" the builtin range_ops operator family, adding my own operator to it, so that I can use it in an exclude index? I couldn't find a SQL definition for the range_ops family, probably because it's defined and implemented in C and header files. -Tobia > On 5 Apr 2016, at 19:30, Tobia Conforto <tobia.conforto@xxxxxxxxxx> wrote: > > Hello > > I'd like to use the Nested Set model[1] to represent a hierarchical data structure in PostgreSQL. I'm considering using a single int4range column instead of the traditional two columns (lft and rgt) because the two values do in fact represent a range of integers. This should allow me to add a constraint to the range column that checks the consistency of the Nested Set model. > > My column is currently: > > pos int4range not null check (lower(pos) between 1 and upper(pos) - 2) > > This is already performing the traditional check of lft >= 1 and rgt > lft, given the canonical form of an int4range. > > Additionally, I would like to add an exclude constraint to validate the Nested Set model: any two ranges must be disjoint (not a.pos && b.pos) OR strictly contained one within the other, without sharing either bound. > > One possible expression for the *exclusion* is that two bad ranges overlap and each is right- or left-bound by the other. I can easily write that as a commutative operator[2]: > > create function bad_nested(int4range, int4range) returns boolean as $$ > begin > return $1 && $2 and ($1 &< $2 or $1 &> $2) and ($2 &< $1 or $2 &> $1); > end; > $$ language 'plpgsql' immutable; > > create operator &/ ( > leftarg = int4range > , rightarg = int4range > , procedure = bad_nested > , commutator = &/ > ); > > Unfortunately, trying to use this operator in an exclude constraint makes PostgreSQL complain about operator families: > > create table test ( > pos int4range not null check (lower(pos) between 1 and upper(pos) - 2) > , exclude using gist (pos with &/) > ); > > ERROR: operator &/(int4range,int4range) is not a member of operator family "range_ops" > Detail: The exclusion operator must be related to the index operator class for the constraint. > > I don't fully understand operator classes / families and I find the manual is being somewhat cryptic about them. > > Can anybody suggest the right create statement(s) to enable using my operator in a GiST index? > > What other "strategies", functions, or operators should I write? The standard ordering of ranges with "<" should be enough, because it maps to the traditional ordering of nested sets. > > Can I do everything in sql / plpgsql or do I need to write C code? > > > -Tobia > > > [1] https://en.wikipedia.org/wiki/Nested_set_model > > [2] There may be simpler / faster expressions for the same condition. A custom plpgsql expression on the lower and upper bounds will probably be faster, if I store the four integers as local variables. But that's just an optimization. > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
