RES: RES: Creating a new type - Mailing list pgsql-interfaces

From Rodrigo Sakai
Subject RES: RES: Creating a new type
Date
Msg-id 002701c69ba4$232d48f0$4700a8c0@TREEZANTHUS
Whole thread Raw
In response to Re: RES: Creating a new type  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-interfaces
<div class="Section1"><p class="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt">>>  So, I have to guarantee that this situation doesn't occur! </span>But I
don't</font><pclass="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt">>>want to use triggers because this check must be done on almost all
tables</span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt">>>that uses the type:</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt">>The fact that you don't want to do it doesn't make it the
wrong</span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt">>solution;-)</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;color:black">In fact it makes the wrong solution! Having to define a
triggeron the table for this field (emp_time) to guarantee this kind of integrity is the same thing to have to define a
triggeron an integer field to guarantee that all data inserted is an integer value!</span></font><p
class="MsoPlainText"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;color:black">Again! Explaining better! I'm dealing with Temporal
Databases(researched by Richard Snodgrass). One of the issues is to maintain the integrity constraints through temporal
data.So, it is necessary that a field doesn't have two different values in the same interval of time, like I
demonstratedin the example! I agree that with triggers this situation is resolved fast and easy! But what I want is
thisfeature became transparent for the user!</span></font><p class="MsoPlainText"><font color="black" face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"><font
color="black"face="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt;color:black">So far, you have
helpeda lot, but do you have other idea?</span></font><p class="MsoPlainText"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt">>At the moment there is no other way than a
trigger. There's been some</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt">>speculationabout whether GIST indexes could be extended to enforce</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt">>conditions like
"nooverlapping values in this column" (as a</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt">>generalization of the concept of unique indexes).  </span>No
one'stried to</font><p class="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt">>makethat happen, though.</span></font><p class="MsoPlainText"><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;color:black"> </span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt">>BTW, you might
wantto look at the "timetravel" code in contrib/spi/</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">>... it's a tad old and crufty, but it sounds like you are trying to do</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt">>something pretty
similarto that.</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;color:black">I didn’t have time to see timetravel yet!</span></font><p
class="MsoPlainText"><fontcolor="black" face="Courier New" size="2"><span
style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText"><font color="black" face="Courier New"
size="2"><spanstyle="font-size:10.0pt;color:black">Thanks again!</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">---------------------------(end of broadcast)---------------------------</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">TIP 6: explain analyze is your friend</span></font></div>

pgsql-interfaces by date:

Previous
From: Tom Lane
Date:
Subject: Re: RES: Creating a new type
Next
From: Matteo Traina
Date:
Subject: Re: jdbc windows