User-Defined Datatypes

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: