Referencing a row from another table (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.

Here is my desired result:

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

class Post{
   int postId;
   ...
}

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

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) ...

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)

Thanks in advance

回答1:

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

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;

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".