Why are Views important in Postgresql?

user_16167140 0
Viewed 62

I'm learning postgresql and just going through the docs. I'm at the section where they are talking about Views. I understand views as they are essentially query blocks (similar to functions in python) which I can call. I also understand when and why I would use them.

My confusion is with this sentence in the docs:

Views allow you to encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces

I'm not sure what that means. When I read that it encapsulates the details I thought it meant views are good for security purposes to hide queries over request.body.

Why are views good for when my application evolves behind consistent interfaces?

postgresql
1 Answers

Views can act as an abstraction layer. Imagine this schema:

CREATE TABLE userdata (
   userid bigint PRIMARY KEY,
   username text UNIQUE NOT NULL,
   favorite_color text NOT NULL
);

CREATE VIEW v_userdata AS
SELECT userid, username, favorite_color
FROM userdata;

The application always accesses the data through the view.

Now at some point you realize that this data model should be improved and normalized. So you change it to

CREATE TABLE color (
   colorid bigint PRIMARY KEY,
   name text UNIQUE NOT NULL
);

CREATE TABLE userdata (
   userid bigint PRIMARY KEY,
   username text UNIQUE NOT NULL,
   favorite_color bigint REFERENCES color NOT NULL
);

CREATE VIEW v_userdata AS
SELECT ud.userid, ud.username,
       c.name AS favorite_color
FROM userdata AS ud
   JOIN color AS c ON ud.favorite_color = c.colorid;

Then you need not change anything in the application, even though the table layout has changed.

user_64643080
Related