Intro to Views
In this episode, we're going to look at Postgres database views, what they are and why you might use them. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - visit us at https://hashrocket.com and www.pgcasts.com
Transcript:
Check out this database, two tables in particular.
```sql
table employees;
table cities;
```
We have an employees table and a cities table, with three records each. As
time has gone on, we've refined a query to join these tables in a useful way.
Let's say it helps the CEO remember everybody's hometown:
```sql
select (first_name || ' ' || last_name) as full_name,
title,
hometown,
state
from employees, cities
where hometown = name;
```
We're doing a couple of things here; concatenating the first name and last name
into a 'full name' string, grabbing some data from the employees table, and
getting the states from the cities table. All to make one useful report.
We repeat this query a handful of times in our code, and our SQL-loving CEO
types it several times a day into psql. It would be great it we could give it a
name.
Enter the database view.
Views give the query a friendly name, letting us refer to it just like any
table. It's really easy:
```sql
create view employee_hometowns as
select (first_name || ' ' || last_name) as full_name,
title,
hometown,
state
from employees, cities
where hometown = name;
```
Now our big query has been simplified to the following:
```sql
select * from employee_hometowns;
```
Does the `table` keyword work, even though it's not really a table?
```sql
table employee_hometowns;
```
You bet. In fact, any query that would work on the table, works on this view:
```sql
select state from employee_hometowns;
```
As noted in the Postgres docs, views are a useful abstraction that belong in a
well-designed database. They encapsulate the logic of your tables behind a
simple interface, decreasing repetition and making everybody's life a little
easier.
That's the overview; to wrap up I'd like to show some production code.
One of the sites we run, 'Today I Learned', has a stats page; check it out at
https://til.hashrocket.com/statistics. Part of that page is a chart of the
'hottest posts', which uses a Reddit-style algorithm to calculate the
unknowable quality of hotness.
Here's the SQL:
```sql
with posts_with_age as (
select *,
greatest(extract(epoch from (current_timestamp - published_at)) / 3600, 0.1) as hour_age
from posts
where published_at is not null
)
select (likes / hour_age ^ 0.8) as score, *
from posts_with_age
order by 1 desc
;
```
This code is a great candidate for a view. It's sufficiently complex; it's
something we'll need to use again and again; and it's interesting enough that
we'd like access to it via psql.
Luckily the author of this code, Josh Davey, agreed, assigning it to a view
called `hot_posts`. Let's see it in action:
```sql
select title from hot_posts limit 5;
```
There you have it, qualified hotness.
Views are powerful. We can make views out of views. We can integrate with
ActiveRecord. We can materialize our views, which stores the results of the
view in the database. All of these subjects are worth exploring in greater
detail in a future episode.
Thanks for watching.
Watch video Intro to Views online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 26 July 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 7,638 once and liked it 116 people.