Re: about PostgreSQL...(important)

On Sat, Jun 02, 2001 at 08:13:57PM +0800, Frank wrote:
> Hi to all:
>     I have a question.
>     PostgreSQL is Object-Relational DBMS . Can I progam Object-Oriented
> Language for PostgreSQL?

  Yes, but.

> Example:
>     I create two tables:
>                 create table Address(
>                               street char(20),
>                               city char(20),
>                               province char(20)
>                  );
>
>                  create table students(
>                                  no int4,
>                                  name char(8),
>                                  address  Address,
>                 )
                                   ^^^^^^^
                   Unfortunately, this won't work as
                   you'd like.

>                insert into Address values ('ZHONG-SHAN','TAIPEI','TAIWAN');
>
>     I find the Address record's oid:
>             =>  select oid from Address where province='TAIWAN';
>
>                                oid
>                              --------
>                               28672
>     Then, I insert a record to students table:
>             =>  insert into students values (8852,'John',28672::Address);

That won't work.  What you might be thinking is to have an "oid" field
in students rather than "address" type.  But, using "oid's" is strongly
discouraged.  Better to use plain ol' typical relational arrangement.
Also, names are generally not case sensitive in PostgreSQL unless
quoted, and I'd recommend against using quoted names (more trouble than
it's worth).  Don't use "name" either, it's semi-reserved in PostgreSQL.

create table address (
    addr_id SERIAL PRIMARY KEY,
    street  varchar(20) NOT NULL CHECK (street <> ''),
    city    varchar(20) NOT NULL CHECK (city <> ''),
    province varchar(20) NOT NULL CHECK (province <> '')
);

create table student (
    sid     integer  PRIMARY KEY,
    sname   varchar(30) NOT NULL CHECK(sname <> ''),
    addr_id integer  NOT NULL REFERENCES address (addr_id)
);

=> BEGIN;
=> INSERT INTO address (street, city, province) values
-> ('ZHONG-SHAN','TAIPEI','TAIWAN');
=> INSERT INTO student (sid, sname, addr_id) values
-> (8852, 'John', select currval('address_addr_id_seq'));
=> COMMIT;
=> SELECT sid, sname, street, city, province FROM student s, address a
-> WHERE s.addr_id = a.addr_id;

>    Finally, I select the  record about the student's province depend on oid:
>             =>select no,name,address.province from students;

Because what you tried to do is not allowed.

--
Eric G. Miller <egm2@jps.net>