Postgres Pro
Downloads
Home   >   mailing lists

Thread: Reference Type in PostgreSQL

Reference Type in PostgreSQL

From
Elena
Date:
Hello all, I'm new in PostgreSQL...

I would want to know like PostgreSQL manages the type reference that defines the standard SQL:1999. I want to define the type of attribute like a reference at other type.

For example, in Oracle8i the definition is:

   -- Type Department
   CREATE OR REPLACE TYPE Department_type AS OBJECT (
      code   NUMBER(5),
      name  VARCHAR(40)
   );

   -- Type Employee 
   CREATE OR REPLACE TYPE Employee_type AS OBJECT (
      code   NUMBER(5),
      name  VARCHAR2(40),
      department REF Department_type -- Reference to Department object type
   );

How can I define it in PostgreSQL? I haven't found it in the manuals.

Thank you for help.

--
Elena



--
Elena

Re: Reference Type in PostgreSQL

From
Jeff Davis
Date:
On Mon, 2007-03-19 at 11:30 +0100, Elena wrote:
> Hello all, I'm new in PostgreSQL...
>
> I would want to know like PostgreSQL manages the type reference that
> defines the standard SQL:1999. I want to define the type of attribute
> like a reference at other type.
>

PostgreSQL doesn't allow references/pointers. OIDs are the closest thing
to a reference in PostgreSQL.

From _An Introduction to Database Systems_ by C.J. Date, p. 872:
"The blunder [The Second Great Blunder] consists of mixing pointers and
relations."

So not everyone thinks that references/pointers in a relation value are
a good idea.

I don't know how the PostgreSQL developers feel about it, but I haven't
seen a lot of demand for this feature on these lists.

Regards,
    Jeff Davis

Re: Reference Type in PostgreSQL

From
"Merlin Moncure"
Date:
On 3/19/07, Elena <elena.planas@gmail.com> wrote:
> Hello all, I'm new in PostgreSQL...
>
> I would want to know like PostgreSQL manages the type reference that defines
> the standard SQL:1999. I want to define the type of attribute like a
> reference at other type.
>
>  For example, in Oracle8i the definition is:
>
>     -- Type Department
>    CREATE OR REPLACE TYPE Department_type AS OBJECT (
>       code   NUMBER(5),
>       name  VARCHAR(40)
>    );
>
>    -- Type Employee
>    CREATE OR REPLACE TYPE Employee_type AS OBJECT (
>       code   NUMBER(5),
>       name  VARCHAR2(40),
>       department REF Department_type -- Reference to Department object type
>    );
>

how is this different from simply nesting the types?

merlin

Re: Reference Type in PostgreSQL

From
Jeff Davis
Date:
On Tue, 2007-03-20 at 10:21 -0400, Merlin Moncure wrote:
> On 3/19/07, Elena <elena.planas@gmail.com> wrote:
> > Hello all, I'm new in PostgreSQL...
> >
> > I would want to know like PostgreSQL manages the type reference that defines
> > the standard SQL:1999. I want to define the type of attribute like a
> > reference at other type.
> >
> >  For example, in Oracle8i the definition is:
> >
> >     -- Type Department
> >    CREATE OR REPLACE TYPE Department_type AS OBJECT (
> >       code   NUMBER(5),
> >       name  VARCHAR(40)
> >    );
> >
> >    -- Type Employee
> >    CREATE OR REPLACE TYPE Employee_type AS OBJECT (
> >       code   NUMBER(5),
> >       name  VARCHAR2(40),
> >       department REF Department_type -- Reference to Department object type
> >    );
> >
>
> how is this different from simply nesting the types?
>

Nesting the types would prevent other tuples from containing a reference
to the same tuple of Department_type.

I don't think a reference is the best thing to do here. A foreign key
fits the relational model much better, and really has no disadvantage
that I can see.

What can a reference do that a foreign key can't?

Regards,
    Jeff Davis