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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
