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

<div class="Section1"><p class="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt"> Ok, let me explain better! In my table example I will store datas like</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt"> </span></font><table
border="0"cellpadding="0" cellspacing="0" class="MsoNormalTable"
style="width:298.7pt;margin-left:2.75pt;border-collapse:collapse"width="398"><tr height="22" style="height:16.5pt"><td
height="22"nowrap style="width:42.2pt;border:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt;height:16.5pt"
valign="bottom"width="56"><p align="center" class="MsoNormal" style="text-align:center"><b><font face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt;font-weight:bold">Emp_id</span></font></b></td><td height="22" nowrap
style="width:57.5pt;border:solidwindowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt;height:16.5pt"
valign="bottom"width="77"><p align="center" class="MsoNormal" style="text-align:center"><b><font face="Times New Roman"
size="3"><spanlang="EN-US" style="font-size:12.0pt;font-weight: bold">emp_name</span></font></b></td><td height="22"
nowrapstyle="width:43.0pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt;height:16.5pt"
valign="bottom"width="57"><p align="center" class="MsoNormal" style="text-align:center"><b><font face="Times New Roman"
size="3"><spanlang="EN-US" style="font-size:12.0pt;font-weight: bold">salary</span></font></b></td><td height="22"
nowrapstyle="width:156.0pt;border: solid windowtext 1.0pt;border-left:none;padding:0cm 3.5pt 0cm 3.5pt; height:16.5pt"
valign="bottom"width="208"><p align="center" class="MsoNormal" style="text-align:center"><b><font face="Times New
Roman"size="3"><span lang="EN-US" style="font-size:12.0pt;font-weight: bold">emp_time</span></font></b></td></tr><tr
height="21"style="height:15.75pt"><td height="21" nowrap style="width:42.2pt;border:solid windowtext 1.0pt;
border-top:none;padding:0cm3.5pt 0cm 3.5pt;height:15.75pt" valign="bottom" width="56" x:num="x:num"><p align="right"
class="MsoNormal"style="text-align:right"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">1</span></font></td><tdheight="21" nowrap style="width:57.5pt;border-top:
none;border-left:none;border-bottom:solidwindowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm
3.5pt;height:15.75pt"valign="bottom" width="77"><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Rodrigo</span></font></td><td height="21" nowrap
style="width:43.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext
1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:15.75pt" valign="bottom" width="57"><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; font-family:Arial">1,200.00</span></font></td><td height="21" nowrap
style="width:156.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid
windowtext1.0pt; padding:0cm 3.5pt 0cm 3.5pt;height:15.75pt" valign="bottom" width="208"><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span lang="EN-US" style="font-size:12.0pt">(2006-jan-01,
2006-jun-01)</span></font></td></tr><trheight="21" style="height:15.75pt"><td height="21" nowrap
style="width:42.2pt;border:solidwindowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt;height:15.75pt"
valign="bottom"width="56" x:num="x:num"><p align="right" class="MsoNormal" style="text-align:right"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">1</span></font></td><td height="21" nowrap
style="width:57.5pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext
1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:15.75pt" valign="bottom" width="77"><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; font-family:Arial">Rodrigo</span></font></td><td height="21" nowrap
style="width:43.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext
1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:15.75pt" valign="bottom" width="57"><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; font-family:Arial">2,100.00</span></font></td><td height="21" nowrap
style="width:156.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid
windowtext1.0pt; padding:0cm 3.5pt 0cm 3.5pt;height:15.75pt" valign="bottom" width="208"><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span lang="EN-US" style="font-size:12.0pt">(2006-mar-01,
2006-sep-01)</span></font></td></tr></table><pclass="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt"> Considering the overlaps of emp_time and this question: what is the salary of employee
Rodrigoon 2006-apr-01 ??? This data is inconsistent because the overlaps of time!</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt">  So, I have to
guaranteethat this situation doesn’t occur! But I don’t want to use triggers because this check must be done on almost
alltables that uses the type:</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt"> </span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">CREATE TYPE time_interval (</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:Arial">    Tbegin date,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size: 
10.0pt;font-family:Arial">    Tend date</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size: 
10.0pt;font-family:Arial">   ) </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 face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt"> And be easy to the user. For example, any table that is created using this type
garanteesthat the overlaps will not occur! To do this I have to get access for the past data of the table, and so, I
haveto know wich table is using the type and have data being inserted!</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt">  Is that a way of doing this?</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt">-----Mensagem
original-----<br/> De: pgsql-interfaces-owner@postgresql.org [mailto:pgsql-interfaces-owner@postgresql.org] Em nome
</span>deTom Lane<br /> Enviada em: quarta-feira, 28 de junho de 2006 12:00<br /> Para: rodrigo.sakai@zanthus.com.br<br
/>Cc: pgsql-interfaces@postgresql.org<br /> Assunto: Re: [INTERFACES] Creating a new type </font><p
class="MsoPlainText"><fontface="Courier New" 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">"Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br> writes:</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">>   So, when I execute an INSERT operation on this table, the functions that</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">> deals with this type need the id or name of the table that the data was</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">> inserted!</span></font><p class="MsoPlainText"><font face="Courier New" 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">Why?  Your example datatype doesn't seem to require any such thing.</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">>   How can I get this ID or NAME inside this functions??? </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">You can't.  The question itself is bogus because it assumes that data</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">values only exist within tables.  You could not have a transient value</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">of the datatype (eg, a function result) because that is not stored in</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">any table.</span></font><p class="MsoPlainText"><font face="Courier New" 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">                  regards, tom lane</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="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 4: Have you searched our list archives?</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">               http://archives.postgresql.org</span></font></div>