Objects in PostgreSQL

PostgreSQL was the first database that introduced objects in relational systems (serialization)... and that is all what I k开发者_如何转开发now about objects and PostgreSQL. I have been doing some research, but frankly didn't find anything good. Is there any good articles/books about it?

---

I'm not sure what you mean with "introducing objects in relational systems". PostgreSQL indeed has custom types, but those are nothing like OOP.

AFAIK the only reason why PostgreSQL is sometimes called an "object-relational database" is because it supports table inheritance. However, the main use case of inheritance has actually been table partitioning; the performance limitations mean that it's not very useful for implementing "object inheritance" (The upcoming PostgreSQL 9.1 release will remove some of these limitations).

Bottom line: Nothing to see here, PostgreSQL is just another relational database.

---

The preface of the Postgres 7 documentation explains why they consider themselves as having pioneered object-relational concepts (in Postgres 8 and later, this got all rephrased/restructured/deleted). The history document gives more details.

---

The historical documents tell the tale, but I was surprised to see that so many commentors mentioned Object Oriented Programming, which is a separate subject entirely.

Postgres started at UC Berkeley as a ground breaking research project, led by Michael Stonebraker, who previously led the Ingres development project there.

The classic example of an Object Relational Database involved storage and retrieval of of non-tabular data, such as images, audio, media, etc. Stonebreaker forsaw the "data explosion", especially in the area of Binary Large Objects, such as images, etc., and realized that the traditional RDBMS was not up to the task.

One of the examples used to describe "the vision" was the need to search an image database, for pictures of sunsets, based on attributes of the data itself, not merely meta-data (names with the string "sunset", labels, etc.). The concept implied the development of revolutionary indexing methods, for example, based on dominant color spectrum (sunsets tend to be red, orange), or other attributes, depending on the data type. These ideas were commercialised in the Illustra product, which was a direct descendant of the original Postgres team's work.

In fact, most of the ORDBMS features were subtracted from the Postgres code base, which became that PostgreSQL that we know today. In that sense, the conclusion is correct. Even PostgreSQL lacks the ORDBMS aspect of the original Postgres.

So, objects in Oracle? Still not there. OOP in an RDBMS? Not the same topic at all.

---

Well, from some point of view, Postgresql can consider the tables entities as composite type which can be seen as objects.

=> SELECT author FROM author;
|      author       |
+-------------------+
| "(1,'john doe')"  |
+-------------------+
| "(2,'Edgar')"     |
+-------------------+

Mixed with Array this can be very powerful

SELECT 
  author.id, 
  author.name,
  array_agg(post) AS posts
FROM 
  author 
    LEFT JOIN post ON 
        author.id = post.author_id 
GROUP BY 
  author.id;
| id |   name   |                  posts                 |
+----+----------+----------------------------------------+
|  1 | John Doe | {"(1,'first post')","(2,'new post')"}  |
+----+----------+----------------------------------------+
|  2 | Edgar    | {"(3,'Hello world')"}                  |
+----+----------+----------------------------------------+

Of course it is not real OOP.

---

As far as I know, first RDBMS offering full scale objects support (the one with user defined types, nesting, master-detail relation etc.) was Oracle. They even added inheritance in later versions, but in my opinion it was an overkill. See example SQL script taken from very old Oracle client installation (more than 15 years old).

drop view department;
drop type dept_t;
drop type emp_list;
drop type emp_t;
create or replace type emp_t as object (
  empno number,
  ename varchar2(80),
  sal   number
);    
/

create or replace type emp_list as table of emp_t;    
/

create or replace type dept_t as object (
  deptno number, 
  dname varchar2(80),
  loc   varchar2(80),
  employees emp_list
);    
/

create or replace view department of dept_t
with object OID (deptno)
as select deptno, dname, loc,
          cast(multiset(select empno, ename, sal 
                        from emp 
                        where emp.deptno = dept.deptno
                        ) as emp_list ) employees
     from dept;

create trigger department_ins
instead of insert on department
for each row 
declare
  emps emp_list;
  emp  emp_t;
begin
  -- Insert the master
  insert into dept( deptno, dname, loc ) 
  values (:new.deptno, :new.dname, :new.loc);

  -- Insert the details
  emps := :new.employees;
  for i in 1..emps.count loop
    emp := emps(i);
    insert into emp(deptno, empno, ename, sal)
    values (:new.deptno, emp.empno, emp.ename, emp.sal);
  end loop;
end;    
/

---

As mentioned by Martin v. Löwis, the old PostgreSQL 7 document simply lists the following 3 points in this topic:

• inheritance (table inheritance)
• data types (PostreSQL supports JSON, Array and hstore)
• functions

IMHO, I guess these features were cutting edge when PostgreSQL introduced them back in the 80s, which, however, are widely available in other databases nowadays. While PostgreSQL still marketing the term "object" and people are confused.