Обсуждение: Reference Type in PostgreSQL
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
);
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
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
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
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