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>