pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRES: Creating a new type
2+ messages / 2 participants
[nested] [flat]
* RES: Creating a new type
@ 2006-06-29 12:38 Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
0 siblings, 1 reply; 2+ messages in thread
From: Rodrigo Sakai @ 2006-06-29 12:38 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: RES: Creating a new type
@ 2006-06-29 13:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-06-29 13:47 UTC (permalink / raw)
To: rodrigo.sakai@zanthus.com.br; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-06-29 13:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-29 12:38 RES: Creating a new type Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
2006-06-29 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox