Скачать с ютуб New in Postgres 9.6: Displaying and Editing Views в хорошем качестве

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