Fwd: Range types (DATERANGE, TSTZRANGE) in a foreign key with "inclusion" logic - Mailing list pgsql-hackers

Hello,
this is a re-post from the SQL user list 2 month ago, because I assume only a developer can answer the questions
below.
Thanks, Matthias Nagel


----------  Weitergeleitete Nachricht  ----------

Betreff: Range types (DATERANGE, TSTZRANGE) in a foreign key with "inclusion" logic
Datum: Mittwoch 23 Januar 2013, 11:28:10
Von: Matthias Nagel <matthias.h.nagel@gmail.com>
An: pgsql-sql@postgresql.org

Hello everybody,

first a big thank you to all that make the range types possible. They are great, especially if one runs a database to
managea student's university dormitory with a lot of temporal information like rental agreements, room allocations,
etc.At the moment we are redesigning our database scheme for PosgreSQL 9.2, because the new range types and especially
the"EXCLUSION" constraints allow to put a lot more (business) logic into the database scheme than before.
 

But there is one feature missing (or I am too stupid to find it).

Let's say we have some kind of container with a lifetime attribute, i.e. something like that

CREATE TABLE container ( id SERIAL PRIMARY KEY, lifetime DATERANGE
);

Further, there are items that must be part of the container and these items have a lifetime, too.

CREATE TABLE item ( id SERIAL PRIMARY KEY, container_id INTEGER, lifetime DATERANGE, FOREIGN KEY (container_id)
REFERENCEScontainer ( id ), EXCLUDE USING gist ( container_id WITH =, lifetime WITH && )
 
);

The foreign key ensures that items are only put into containers that really exist and the exclude constraint ensure
thatonly one item is member of the same container at any point of time.
 

But actually I need a little bit more logic. The additional contraint is that items must only be put into those
containerswhose lifetime covers the lifetime of the item. If an item has a lifetime that exceeds the lifetime of the
container,the item cannot be put into that container. If an item is already in a container (with valid lifetimes) and
laterthe container or the item is updated such that either lifetime is modified and the contraint is not fullfilled any
more,this update must fail.
 

I would like to do someting like:

FOREIGN KEY ( container_id, lifetime ) REFERENCES other_table ( id, lifetime ) USING gist ( container_id WITH =,
lifetimeWITH <@ )
 

(Of course, this is PosgreSQL-pseudo-code, but it hopefully make clear what I want.)

So, now my questions:

1) Does this kind of feature already exist in 9.2? If yes, a link to the documentation would be helpful.

2) If this feature does not directly exist, has anybody a good idea how to mimic the intended behaviour?

3) If neither 1) or 2) applies, are there any plans to integrate such a feature? I found this discussion
http://www.postgresql.org/message-id/4F8BB9B0.5090708@darrenduncan.net. Does anybody know about the progress?
 

Having range types and exclusion contraints are nice, as I said in the introdruction. But if the reverse (foreign key
withinclusion) would also work, the range type feature would really be amazing.
 


Best regards, Matthias Nagel



----------------------------------------------------------------------
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe

Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias.h.nagel@gmail.com
ICQ: 499797758
Skype: nagmat84

-------------------------------------------------------------
----------------------------------------------------------------------
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe

Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias.h.nagel@gmail.com
ICQ: 499797758
Skype: nagmat84