Objects in PostgreSQL

Question

PostgreSQL was the first database to inject objects into relational systems (serialization) ... and that’s all I know about objects and PostgreSQL. I did some research, but frankly did not find anything good. Are there any good articles / books about this?

Answer 1

The preface to the Postgres 7 documentation explains why they consider themselves pioneers of object-relational concepts (in Postgres 8 and later, this is all reworded / restructured / deleted). The history document provides more details.

Answer 2

I'm not sure what you mean by "introducing objects into relational systems." PostgreSQL does have custom types, but they are not like OOP.

AFAIK is the only reason PostgreSQL is sometimes called an "object-relational database" because it supports table inheritance . However, the main advantage of inheritance was table partitioning ; performance limitations mean that it’s not very useful for implementing “object inheritance” (the upcoming release of PostgreSQL 9.1 will remove some of these limitations ).

Bottom line: nothing is visible here, PostgreSQL is another relational database.

Answer 3

Historical documents tell a story, but I was surprised to see that many commentators mentioned object-oriented programming, which is a separate subject in its entirety.

Postgres started at UC Berkeley as a fundamental research project led by Michael Stonebreaker, who had previously led the Ingres development project.

A classic example of an object relational database included storing and retrieving non-tabular data such as images, audio, multimedia, etc. Stonebreaker surpassed the "data explosion", especially in the field of Binary Large Objects, such as images, etc., and realized that the traditional DBMS could not cope.

One example used to describe a “vision” was the need to search the image database for sunset images based on the attributes of the data itself, and not just metadata (names with the string “sunset”, labels, etc.). The concept implied the development of revolutionary indexing methods, for example, based on the dominant color spectrum (sunsets, usually red, orange) or other attributes, depending on the type of data. These ideas were commercialized in Illustra, which was a direct descendant of the original work of the Postgres team.

In fact, most of the ORDBMS functions were subtracted from the Postgres database, which became the PostgreSQL we know today. In this sense, the conclusion is correct. Even PostgreSQL lacks the ORDBMS aspect of the original Postgres.

So, objects in Oracle? Not yet. OOP in a DBMS? Not the same topic at all.

Answer 4

Well, from some points of view, Postgresql can consider table entities as composite, which can be considered as objects.

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

Mixed with an array, it 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, this is not a real OOP.

Answer 5

As far as I know, the first DBMS to offer support for full-blown objects (one with user-defined types, an attachment, the relationship between the main parts, etc.) was Oracle. They even added inheritance to later versions, but in my opinion, it was too much. See an example SQL script taken from a very old installation of an Oracle client (over 15 years).

 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; /