Transcript

Check out this database, two tables in particular.

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:

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:

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:

select * from employee_hometowns;

Does the table keyword work, even though it's not really a table?

table employee_hometowns;

You bet. In fact, any query that would work on the table, works on this view:

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:

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:

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.

Setup

-- create employees table
create table employees (
  hometown varchar(80),
  first_name varchar,
  last_name varchar,
  title varchar
);

-- create cities table
create table cities (
  name varchar(80),
  state varchar(2)
);

-- populate data
insert into employees values 
('White Haven', 'Liz', 'Lemon', 'Head Writer'),
('Stone Mountain', 'Kenneth', 'Parcell', 'Page'),
('Sadchester', 'Jack', 'Donaghy', 'Vice President of East Coast Television and Microwave Oven Programming');

insert into cities values 
('White Haven', 'PA'),
('Stone Mountain', 'GA'),
('Sadchester', 'MA');