Home > mailing lists

Thread: Creating a new type

Creating a new type

From

"Rodrigo Sakai"

Date:

28 June 2006, 14:49:53

Hi all,

I have to create a new composed data type to use in almost all tables of my database. Something like:

CREATE TYPE time_interval (

Tbegin date,

Tend date

)

And when use this new type in my tables! So, I have to be able to get the table_name or table_id that the type is in. For exemple:

CREATE TABLE employee (

Emp_id int,

Emp_name varchar(30),

Emp_time time_interval

)

So, when I execute an INSERT operation on this table, the functions that deals with this type need the id or name of the table that the data was inserted!

How can I get this ID or NAME inside this functions???

Thanks!!!

Re: Creating a new type

From

Tom Lane

Date:

28 June 2006, 14:59:53

"Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br> writes:
>   So, when I execute an INSERT operation on this table, the functions that
> deals with this type need the id or name of the table that the data was
> inserted!

Why?  Your example datatype doesn't seem to require any such thing.

>   How can I get this ID or NAME inside this functions??? 

You can't.  The question itself is bogus because it assumes that data
values only exist within tables.  You could not have a transient value
of the datatype (eg, a function result) because that is not stored in
any table.
        regards, tom lane

RES: Creating a new type

From

"Rodrigo Sakai"

Date:

29 June 2006, 12:38:16

<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>

Re: RES: Creating a new type

From

Tom Lane

Date:

29 June 2006, 13:47:12

"Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br> writes:
>   So, I have to guarantee that this situation doesn't occur! But I don't
> want to use triggers because this check must be done on almost all tables
> that uses the type:

The fact that you don't want to do it doesn't make it the wrong
solution ;-)

At the moment there is no other way than a trigger.  There's been some
speculation about whether GIST indexes could be extended to enforce
conditions like "no overlapping values in this column" (as a
generalization of the concept of unique indexes).  No one's tried to
make that happen, though.

BTW, you might want to look at the "timetravel" code in contrib/spi/
... it's a tad old and crufty, but it sounds like you are trying to do
something pretty similar to that.
        regards, tom lane

RES: RES: Creating a new type

From

"Rodrigo Sakai"

Date:

29 June 2006, 17:47:45

<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>