New in Postgres 9.6: Displaying and Editing Views

Описание: Two psql commands in Postgres 9.6 for displaying and editing views. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com

Transcript:

If you haven't seen our introduction to database views, check out the link in
the transcript (   • Intro to Views  ). 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.

```sql
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:

```sql
\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.

```sql
\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`:

```sql
\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.

```sql
\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.

```sql
--- 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.

```sql
--- 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:

```sql
\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:    • Intro to Views  
Create View: https://www.postgresql.org/docs/9.3/s...