Re: 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 manage a 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) REFERENCES container ( 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 that only 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 containers whose 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 later the 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 =, lifetime WITH <@ )

(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 with inclusion) 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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers