How to View/Edit Existing View

abeltz

• April 11, 2018 02:05

Once I create a view, is there a way for me to look at the underlying SQL of that view?

0

• 

  abeltz

  • April 13, 2018 04:18

  I received follow-up from the support team:

  there is a table called: pg_views which contains the definition of the view.

  Side Note: At one time I believe I could edit the view directly on the screen similar to if I look at a table but maybe I’m imagining things.

  Either way I believe this is closed as there is a method to retrieving the information on it.

  0
• 

  Alon Brody

  • April 27, 2018 01:06

  Hi Adam,

  Here’s a bit of additional detail. As you noted, if the view still exists, you can find it in pg_views system table.

  Run this query, which will fetch all views under the public schema, and find the definition of the desired view:

  SELECT
    *
  FROM
    pg_views
  WHERE
    schemaname = 'public';
  

  To fetch and reproduce the create view SQL statement, you can use below query:

  SELECT
    'CREATE VIEW ' + schemaname + '.' + viewname + ' AS ' + definition
  FROM
    pg_views
  WHERE
    schemaname = '<schema name>'
  AND
    viewname = '<view name>';
  

  0

Please sign in to leave a comment.