Thread: User-Defined Datatypes
Hello.
Is it possible to create new structured datatypes in PostgreSQL like in this IBM UDB2 statement:
create type person_t as (
name varchar(30),
car car_t)
create type car_t as (
model varchar(30),
plate carchar(20))
create table car of car_t
create table person of person_t
where the table person contains a reference to the car-type in table car?
In the reference manuel I only found the create-table-statement, which automatically creates a new data-typ corresponding to the table. But is it possible to implement the above model including the reference by making only create-table-statements?
Thanks for a quick answer.
Philip Reimer
Philip Reimer wrote: > Hello. > > Is it possible to create new structured datatypes in PostgreSQL like in > this IBM UDB2 statement: Hehe, absolutely. AFAIK, Postgres was the first database system to implement such a feature. IBM got that, I think, through Illustra via Informix, and guess what Illustra is based on? Postgres of course :-) See CREATE TYPE for more detail. -Gunther -- Gunther Schadow, M.D., Ph.D. gschadow@regenstrief.org Medical Information Scientist Regenstrief Institute for Health Care Adjunct Assistant Professor Indiana University School of Medicine tel:1(317)630-7960 http://aurora.regenstrief.org
Philip Reimer <phre@wi.uni-muenster.de> writes:
> Is it possible to create new structured datatypes in PostgreSQL like in this
> IBM UDB2 statement:
> create type person_t as (
> name varchar(30),
> car car_t)
> create type car_t as (
> model varchar(30),
> plate carchar(20))
> create table car of car_t
> create table person of person_t
We don't support that syntax, but you can achieve approximately the same
effect using inheritance:
create table person_t ( ... );
create table person () inherits(person_t);
Very often, the parent table of an inheritance relationship isn't
intended to ever actually contain any rows itself. In that case the
parent is effectively serving as a datatype, or at least you could
think of it that way.
regards, tom lane
Philip Reimer wrote: > Hello. > > Is it possible to create new structured datatypes in PostgreSQL like in > this IBM UDB2 statement: oops, now I'm confused. Was that feature taken out when Postgres became PostgreSQL? CREATE TYPE speaks only of opaque/scalar types. I think in PostgreSQL you could use any table as a type name and the type of a column was then automatically converted into a foreign key kind of thing. For the sake of portability, I would actually always prefer that unless the RDBMS provides a feature to cluster/inline optimize these type tables for the physical storage. But then, a good RDBMs should have the logical model independent from physical storage model anyway, but anbout noone seems to care and everyone continues to bloat the SQL language with features that should in fact be options of the RDBMS physical storage manager to (auto-) configure. So, I guess the answer is no for PostgreSQL. I remember that even in old Postgres I couldn't actually insert composite data very easily. regards -Gunther -- Gunther Schadow, M.D., Ph.D. gschadow@regenstrief.org Medical Information Scientist Regenstrief Institute for Health Care Adjunct Assistant Professor Indiana University School of Medicine tel:1(317)630-7960 http://aurora.regenstrief.org
Illustra (rip) did support create type in this way.
It is a sad failing for postgreSQL that it does not
enable creation of composite types and inherited
types (other than tables). The ability to store a
row (or rows!) in a column was a useful and valuable
idea.
You can of course create your own datatype in C
in postgreSQL but you must contain each subreference
with accessor functions.
There doesn't seem to be an emphasis on the ability
of postgreSQL to create a more full featured type system.
I would use a function that created tuples in a second.
The ability to subtype existing base data types made
creating uniquely sorted or manipulated types very easy.
I do however understand the underlying reasons why postgreSQL
has not gone the way illustra did in this way. Technically,
some of it is tricky (but possible!) and because of slow adoption
only us die-hard OR people really used the capabilities.
PostgreSQL seems to be turning away from its OR roots into
more of a transactional db system. I, for one, use postgreSQL
because it is an ORDBMS so this is sad. With the gobbling
of illustra-informix-ibm, extensibility is an asset that can and should
stand out with the database community.
elein@nextbus.com
and not or
(PS: I can write functional specs if someone wants to implement
any of an extended type system :-)
At 03:04 PM 4/15/2002 -0400, Tom Lane wrote:
>Philip Reimer <phre@wi.uni-muenster.de> writes:
> > Is it possible to create new structured datatypes in PostgreSQL like in
> this
> > IBM UDB2 statement:
>
> > create type person_t as (
> > name varchar(30),
> > car car_t)
>
> > create type car_t as (
> > model varchar(30),
> > plate carchar(20))
>
> > create table car of car_t
> > create table person of person_t
>
>We don't support that syntax, but you can achieve approximately the same
>effect using inheritance:
>
> create table person_t ( ... );
>
> create table person () inherits(person_t);
>
>Very often, the parent table of an inheritance relationship isn't
>intended to ever actually contain any rows itself. In that case the
>parent is effectively serving as a datatype, or at least you could
>think of it that way.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster