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

  1. One comment

  2. # Petr Korobeinikov
    Jul 21, 2015

    Do not forget that it doesn’t support editing for recursive views =(
    At least for now.

Leave a comment