PostgreSQL 中的对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4700036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Objects in PostgreSQL
提问by Lukasz Madon
PostgreSQL was the first database that introduced objects in relational systems (serialization)... and that is all what I know 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?
PostgreSQL 是第一个在关系系统（序列化）中引入对象的数据库……这就是我对对象和 PostgreSQL 的全部了解。我一直在做一些研究，但坦率地说没有发现任何好的东西。有没有关于它的好文章/书籍？
回答by intgr
I'm not sure what you mean with "introducing objects in relational systems". PostgreSQL indeed has custom types, but those are nothing like OOP.
我不确定“在关系系统中引入对象”是什么意思。PostgreSQL 确实有自定义类型，但它们与 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).
AFAIK PostgreSQL 有时被称为“对象关系数据库”的唯一原因是因为它支持表继承。然而，继承的主要用例实际上是表分区；性能限制意味着它对于实现“对象继承”不是很有用（即将发布的 PostgreSQL 9.1 版本将删除其中一些限制）。
Bottom line: Nothing to see here, PostgreSQL is just another relational database.
底线：这里没什么可看的，PostgreSQL 只是另一个关系数据库。
回答by Martin v. L?wis
The preface of the Postgres 7documentation explains why they consider themselves as having pioneered object-relational concepts (in Postgres 8 and later, this got all rephrased/restructured/deleted). The history documentgives more details.
Postgres 7文档的前言解释了为什么他们认为自己是对象关系概念的先驱（在 Postgres 8 及更高版本中，这一切都被改写/重组/删除）。该历史文件提供了更多的细节。
回答by Noah F. SanTsorbutz
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.
Postgres 始于加州大学伯克利分校，是一个开创性的研究项目，由迈克尔·斯通布雷克 (Michael Stonebraker) 领导，他曾在那里领导过 Ingres 开发项目。
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.
对象关系数据库的经典例子涉及非表格数据的存储和检索，例如图像、音频、媒体等。 Stonebreaker 预见了“数据爆炸”，特别是在二进制大对象领域，例如图像、等，并意识到传统的 RDBMS 无法胜任这项任务。
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.
用于描述“视觉”的示例之一是需要根据数据本身的属性，而不仅仅是元数据（带有字符串“日落”的名称、标签等）在图像数据库中搜索日落图片.) 该概念暗示了革命性索引方法的发展，例如，基于主要色谱（日落往往是红色、橙色）或其他属性，具体取决于数据类型。这些想法在 Illustra 产品中被商业化，它是原始 Postgres 团队工作的直接后代。
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.
事实上，大部分 ORDBMS 功能都从 Postgres 代码库中减去，这成为我们今天所知道的 PostgreSQL。从这个意义上说，结论是正确的。甚至 PostgreSQL 也缺少原始 Postgres 的 ORDBMS 方面。
So, objects in Oracle? Still not there. OOP in an RDBMS? Not the same topic at all.
那么，Oracle 中的对象呢？还是不在。RDBMS 中的 OOP？根本不是同一个话题。
回答by greg
Well, from some point of view, Postgresql can consider the tables entities as composite type which can be seen as objects.
好吧，从某些角度来看，Postgresql 可以将表实体视为可以视为对象的复合类型。
=> SELECT author FROM author;
| author |
+-------------------+
| "(1,'john doe')" |
+-------------------+
| "(2,'Edgar')" |
+-------------------+
Mixed with Array this can be very powerful
与 Array 混合，这可以非常强大
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.
当然，它不是真正的 OOP。
回答by andrey42
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).
据我所知，第一个提供全面对象支持（具有用户定义类型、嵌套、主从关系等的 RDBMS）是 Oracle。他们甚至在以后的版本中添加了继承，但在我看来这是一种矫枉过正。请参阅从非常旧的 Oracle 客户端安装（超过 15 年）中获取的示例 SQL 脚本。
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;
/
回答by yetsun
As mentioned by Martin v. L?wis, the old PostgreSQL 7 documentsimply lists the following 3 points in this topic:
正如 Martin v. L?wis 所提到的，旧的 PostgreSQL 7 文档在这个主题中简单地列出了以下 3 点：
- inheritance (table inheritance)
- data types (PostreSQL supports JSON, Array and hstore)
- functions
- 继承（表继承）
- 数据类型（PostreSQL 支持 JSON、Array 和 hstore）
- 职能
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.
恕我直言，我猜这些特性在 80 年代 PostgreSQL 引入它们时是最前沿的，然而，这些特性现在在其他数据库中广泛可用。虽然 PostgreSQL 仍在宣传“对象”一词，但人们对此感到困惑。
