引用另一个表中的行（PostgreSQL） [英] Referencing a row from another table (PostgreSQL)

问题描述

我是PostgreSQL的新手，我正努力了解如何将对表中实例（或行）的引用用作另一个表的一行中的值。

I'm new to PostgreSQL and I'm struggling to understand how to use a reference to an 'instance' (or row) from a table as a value within a row of another table.

这是我想要的结果：

class User{
   int age;
   Post[] posts;
}

class Post{
   int postId;
   ...
}

// Sql script
sqlMain{
    User k = new User(20);
    k.addPost(10, ...);
}

如您所见，我想要一个（动态优先，例如ArrayList）帖子数组作为用户的属性。

As you can see, I want an (dynamic prefereably, like an ArrayList) array of posts as an attribute of a user.

到目前为止，我有以下脚本：

So far I have the following Script:

CREATE TABLE Post(
    postId INT
)

CREATE TABLE User(
    id INT,
    posts Post[]
)

// Member function of User class
CREATE FUNCTION addPost(postId int) ...

由于PostgreSQL是ORDBMS，我是否可以假设采用以下方法

As PostgreSQL is an ORDBMS, am I right to assume the following approach will be possible

(SELECT *row* FROM User WHERE id = 10).addPost(20)

预先

推荐答案

如果我对您的理解正确，则应阅读有关关系数据库的基本概念（即 http://www3.ntu.edu.sg/home/ehchua/programming/sql/ Relational_database_design.html ）。您的表应如下所示：

If I understand you correctly, you should read about the basic concepts of relational databases (i.e. http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html). Your tables should look like this:

CREATE TABLE post(
    post_id INT,
    user_id INT
);

CREATE TABLE user (
    user_id INT
);

这基本上是用户与帖子之间的一对多关系，这意味着一个用户可以拥有许多职位。如果您想要某个用户的所有帖子（在本例中是ID为1的用户），则可以这样获得它们：

This is basically a one-to-many relationship between user and post, meaning that one user can have many posts. If you want all posts of a user (in this case the user with id 1), you could get them like this:

SELECT * FROM user u
LEFT JOIN post p ON u.user_id = p.user_id
WHERE user_id = 1;

正如我在您的问题中所看到的，您可能希望将结果映射到面向对象的模型。这在很大程度上取决于您使用的技术/语言。大多数技术都提供了用于连接到PostgreSQL之类的数据库系统的库，打开和关闭连接会启动查询并获取结果。在这种情况下，您必须自己映射结果。但是也有像冬眠这样的高级映射器试图为您完成这项工作。但是要使用它们，您应该对幕后技术有很好的了解。

As I can see in your question, you might want to map the result to an object oriented model. This depends a lot on the technology/language you are using. The majority of technologies offer libraries to connect to database systems like PostgreSQL, open and close connections launch queries and get back the results. In this case you have to map the results yourself. But there are also advanced mappers like hibernate trying to do this work for you. But to use them, you should have a good knowledge of the technologies "under the hood".

这篇关于引用另一个表中的行（PostgreSQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！