Transcript

If you haven't seen our introduction to database views, check out the link in the transcript. To recap, views are an abstraction that encapsulate the logic of complex queries behind a simple interface.

Today we're going to be looking at two new commands in Postgres 9.6 which help us better understand and change our views.

To prepare, I've set up a sample database with two tables, employees and hometowns, and a view that joins them called employee_hometowns. The script is included below.

It's pretty straightforward to see what a view produces, by selecting from it just like any other table.

select * from employee_hometowns;

But what if we want to see the composition of the view? Well, we can display the view just like a table: 

\d employee_hometowns

But this is only part of the story. It only shows what the table returns, not how it is generated.

Postgres 9.6 introduced a new command to address the very issue, \sv, or 'show view'. Let's try it out.

\sv employee_hometowns

There's our view; pretty cool. Use this on any view to see what it's made of, instead of browsing through migrations and script files.

This API was designed to complement the existing command for showing functions. Here's that command for the function now: 

\sf now

Great, so we can see our views, and understand their composition.

But what if we want to edit our views, too?

Postgres 9.6 to the rescue. Replace the s with e, and we have our edit command.

\ev employee_hometowns

This will open an edit buffer in your default text editor.

Editing is interesting, and has some definite edge cases. One thing you can certainly add are columns. Let's do that and see the result.

--- add country to view...
select * from employee_hometowns;

Now we can see that our view has a column for country.

Changing the views name is problematic though, because if we look at the SQL output from our previous command, it creates or replaces a view based on its name. And so, changing the name will just create a new view with the new name.

--- rename view...
\d employee_hometowns
\d employee_hometowns_with_country

Finally, we can't remove or rename columns that are returned by the view. Case in point: 

\ev employee_hometowns
--- try to rename a column...

Why isn't this possible? Let's consult the Postgres docs: 

The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

In the case where you must rename a view or its columns, just drop the view and then create it again.

That's all for this episode; thanks for watching.

Resources

• Intro to Views
• Create View

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),
  country 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', 'US'),
('Stone Mountain', 'GA', 'US'),
('Sadchester', 'MA', 'US');

-- create view
create view employee_hometowns as
select (first_name || ' ' || last_name) as full_name,
  title,
  hometown,
  state
from employees, cities
where hometown = name;