Обсуждение: Creating a new type
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!!!
"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
Ok, let me explain better! In my table example I will store datas like
Emp_id | emp_name | salary | emp_time |
1 | Rodrigo | 1,200.00 | (2006-jan-01, 2006-jun-01) |
1 | Rodrigo | 2,100.00 | (2006-mar-01, 2006-sep-01) |
Considering the overlaps of emp_time and this question: what is the salary of employee Rodrigo on 2006-apr-01 ??? This data is inconsistent because the overlaps of time!
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:
CREATE TYPE time_interval (
Tbegin date,
Tend date
)
And be easy to the user. For example, any table that is created using this type garantees that the overlaps will not occur! To do this I have to get access for the past data of the table, and so, I have to know wich table is using the type and have data being inserted!
Is that a way of doing this?
-----Mensagem original-----
De: pgsql-interfaces-owner@postgresql.org [mailto:pgsql-interfaces-owner@postgresql.org] Em nome de Tom Lane
Enviada em: quarta-feira, 28 de junho de 2006 12:00
Para: rodrigo.sakai@zanthus.com.br
Cc: pgsql-interfaces@postgresql.org
Assunto: Re: [INTERFACES] Creating a new type
"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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
"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
>> 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 ;-)
In fact it makes the wrong solution! Having to define a trigger on the table for this field (emp_time) to guarantee this kind of integrity is the same thing to have to define a trigger on an integer field to guarantee that all data inserted is an integer value!
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 demonstrated in the example! I agree that with triggers this situation is resolved fast and easy! But what I want is this feature became transparent for the user!
So far, you have helped a lot, but do you have other idea?
>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.
I didn’t have time to see timetravel yet!
Thanks again!
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend