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,
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:
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,
‘show view’. Let’s try it out.
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
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
e, and we have our edit
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.
-- 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'); insert into employees values ('Stone Mountain', 'Kenneth', 'Parcell', 'Page'); insert into employees values ('Sadchester', 'Jack', 'Donaghy', 'Vice President of East Coast Television and Microwave Oven Programming'); insert into cities values ('White Haven', 'PA', 'US'); insert into cities values ('Stone Mountain', 'GA', 'US'); insert into cities values ('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;