From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Kenneth Downs <ken(at)secdat(dot)com> |
Cc: | Harald Armin Massa <haraldarminmassa(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: challenging constraint situation - how do I make it |
Date: | 2006-05-24 14:55:12 |
Message-ID: | 447473D0.8020402@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Kenneth Downs wrote:
> Alban Hertroys wrote:
> The approach I tried was to have a "range" or "interval" type. You
> place a column into a table named "resv_date" or whatever and it would
> expand the definition into two columns, you'd get resv_date_beg and
> resv_date_end. If you declared the "resv_date" column a primary key
> column, it would build trigger code to detect overlaps and nesting and
> reject those.
>
> As I said, defining behavior and implementing it was not hard. I even
> had foreign keys into ranges that were "smart". If the foreign key was
> a single column instead of two, it would satisfy RI if the single value
> was between the interval values in the parent table.
Been there, done that ;)
> The problem comes from the split-personality of the "resv_date" column.
> Sometimes its one column, sometimes its two. This made writing the
> tools nasty and difficult, and I scratched it and (gasp!) did some
> validation in client code.
What's the benefit of allowing it to be only one column?
> I have it in mind to restore the feature, but in a different way. The
> two columns should be defined separately, not as one, and then the
> second of the two gets a flag setting, like:
>
> column range_beg { primary_key: Y; }
> column range_end { primary_key: Y; range_from: range_beg; }
>
> The "range_from" setting ties one column to the other and should give me
> all the behavior I had without all of the confusion. It would have
> three effects:
>
> 1) Force range_end >= range_beg
> 2) Convert the primary key into overlap/nest exclusion
> 3) Allow a single column foreign key in another table to "know" that it
> should do a within match instead of an equality match
And making that one column of a composite type would be just the thing,
I thought somewhere at the start of this thread (Thanks for mentioning
"composite types", Florian, couldn't remember what they're called).
Cheers,
--
Alban Hertroys
alban(at)magproductions(dot)nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
From | Date | Subject | |
---|---|---|---|
Next Message | Sim Zacks | 2006-05-24 15:17:03 | Re: background triggers? |
Previous Message | Rafal Pietrak | 2006-05-24 14:54:03 | Re: background triggers? |