In this episode, we're going to look at Postgres database views, what they are and why you might use them.
Postgres Version 9.5
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;
table keyword work, even though it's not really a table?
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
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.
-- 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');