On 3rd of July, Tom Lane committed patch:
Add psql \ev and \sv commands for editing and showing view definitions. These are basically just like the \ef and \sf commands for functions. Petr Korobeinikov, reviewed by Jeevan Chalke, some changes by me
It's not a huge patch, but it's the first patch for Pg 9.6 series that I wanted to write about 🙂
So, some of you know that you can see function body by doing \df+, like here:
$ \df+ test List OF functions -[ RECORD 1 ]-------+--------------------- Schema | public Name | test RESULT DATA TYPE | INTEGER Argument DATA types | a INTEGER, b INTEGER TYPE | normal Security | invoker Volatility | volatile Owner | depesz LANGUAGE | SQL SOURCE code | SELECT a + b; Description |
(check the “source code" line).
You can also \sf to view function definition:
$ \sf test CREATE OR REPLACE FUNCTION public.test(a INTEGER, b INTEGER) RETURNS INTEGER LANGUAGE SQL AS $function$ SELECT a + b; $function$
And you can also do \ef … to start your favorite editor with the function definition opened and ready to be edited.
Now, with the patch that was committed on 3rd of July, you can do the same, for views:
$ \sv table_sizes CREATE OR REPLACE VIEW public.table_sizes AS SELECT n.nspname AS schema_name, c.relname AS TABLE_NAME, pg_table_size(c.oid::regclass) AS table_size, pg_total_relation_size(c.oid::regclass) AS total_table_size FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r'::"char"
and \ev of course too (I'm not showing it, as it's relatively complicated to show that editor is started, but it is, trust me :).
Just like with \ef, there is no final “;" at the end of the command, so it's pretty safe.
While I generally don't modify object in database manually (I prefer preparation of patch files, and applying them with versioning), it will definitely be helpful when fiddling with dev databases or temporary views. Cool, thanks.