Re: Fwd: Range types (DATERANGE, TSTZRANGE) in a foreign key with "inclusion" logic

Hi,

Far as I am aware - there is not yet described FK feature...

But should be possible to ensure that rule via trigger.. 1 after update on
container, and one after insert/update on item...

Kind Regards,

Misa

On Saturday, April 6, 2013, Matthias Nagel wrote:

> 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(dot)h(dot)nagel(at)gmail(dot)com <javascript:;>>
> An: pgsql-sql(at)postgresql(dot)org <javascript:;>
>
> 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(dot)h(dot)nagel(at)gmail(dot)com <javascript:;>
> 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(dot)h(dot)nagel(at)gmail(dot)com <javascript:;>
> ICQ: 499797758
> Skype: nagmat84
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org<javascript:;>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>