blocks|key|387359|text|一直不得不回到这里来查找pg_get_viewdef+(如何记住它！！)，所以搜索了一个更容易记住的命令……我明白了：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|387360|\d%2B+viewname|code-block|syntax|javascript|387361|您可以通过在pgsql命令行中输入\?来查看类似类型的命令。|387362|额外提示:+emacs命令sql-postgres使pgsql更加友好(编辑、复制、粘贴、命令历史记录)。|387363|entityMap^0|C|E|0|0|H|2|0|D|C|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

Kept having to return here to look up <code>pg_get_viewdef</code> (how to remember that!!), so searched for a more memorable command... and got it:

<pre><code>\d+ viewname
</code></pre>

You can see similar sorts of commands by typing <code>\?</code> at the pgsql command line.

Bonus tip: The emacs command <code>sql-postgres</code> makes pgsql a lot more pleasant (edit, copy, paste, command history).

blocks|key|387297|text|select+pg_get_viewdef('viewname',+true)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|387298|手册中提供了所有这些功能的列表：|unstyled|387299|http://www.postgresql.org/docs/current/static/functions-info.html|offset|length|387300|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|1T|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|T|8|@]|9|@$I|U|J|V|1|W]]|A|$]]|$1|K|3|-4|5|F|7|X|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|H]]]]

<pre><code>select pg_get_viewdef('viewname', true)
</code></pre>

A list of all those functions is available in the manual: 

<a href="http://www.postgresql.org/docs/current/static/functions-info.html" rel="noreferrer">http://www.postgresql.org/docs/current/static/functions-info.html</a>

blocks|key|387260|text|select+definition+from+pg_views+where+viewname+=+'my_view'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|387261|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select definition from pg_views where viewname = 'my_view'
</code></pre>

blocks|key|387380|text|如果您需要ANSI+SQL-92版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|387381|select+view_definition+from+information_schema.views+where+table_name+=+'view_name';|code-block|syntax|javascript|387382|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you want an ANSI SQL-92 version:

<pre><code>select view_definition from information_schema.views where table_name = 'view_name';
</code></pre>

blocks|key|169344|text|来自v9.6和更高版本的好消息。视图编辑现在是从psql原生的。只需调用\ev命令。视图定义将显示在您配置的编辑器中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|169345|julian@assange=#+\ev+your_view_names|169346|奖金。一些用于与查询缓冲区交互的有用命令。|169347|Query+Buffer
++\e+[FILE]+[LINE]+++++++edit+the+query+buffer+(or+file)+with+external+editor
++\ef+[FUNCNAME+[LINE]]++edit+function+definition+with+external+editor
++\ev+[VIEWNAME+[LINE]]++edit+view+definition+with+external+editor
++\p+++++++++++++++++++++show+the+contents+of+the+query+buffer
++\r+++++++++++++++++++++reset+(clear)+the+query+buffer
++\s+[FILE]++++++++++++++display+history+or+save+it+to+file
++\w+FILE++++++++++++++++write+query+buffer+to+file|code-block|syntax|javascript|169348|entityMap^0|10|3|0|0|10|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|W|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|X|8|@]|D|@]|E|$M|N]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

Good news from v9.6 and above. View editing are now native from psql. Just invoke <code>\ev</code> command. View definitions will show in your configured editor.
<code>julian@assange=# \ev your_view_names</code>
Bonus. Some useful command to interact with query buffer.
<pre><code>Query Buffer
 \e [FILE] [LINE] edit the query buffer (or file) with external editor
 \ef [FUNCNAME [LINE]] edit function definition with external editor
 \ev [VIEWNAME [LINE]] edit view definition with external editor
 \p show the contents of the query buffer
 \r reset (clear) the query buffer
 \s [FILE] display history or save it to file
 \w FILE write query buffer to file
</code></pre>

blocks|key|169367|text|在psql+cli中，您可以使用|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|169368|\d%2B+<yourViewName>
\sv+<yourViewName>|code-block|syntax|javascript|169369|输出如下：|169370|\d%2B+v_ma_students

+++++++++++++++++++++++++++++++View+"public.v_ma_students"
+Column+%7C+++++++++Type++++++++++%7C+Collation+%7C+Nullable+%7C+Default+%7C+Storage++%7C+De
scription
--------%2B-----------------------%2B-----------%2B----------%2B---------%2B----------%2B---
SOMETHINGS+HERE

View+definition:
+SELECT+student.sno,
++++student.sname,
++++student.ssex,
++++student.sage,
++++student.sdept
+++FROM+student
++WHERE+student.sdept::text+=+'MA'::text;
Options:+check_option=cascaded


\sv+v_ma_students

CREATE+OR+REPLACE+VIEW+public.v_ma_students+AS
+SELECT+student.sno,
++++student.sname,
++++student.ssex,
++++student.sage,
++++student.sdept
+++FROM+student
++WHERE+student.sdept::text+=+'MA'::text
+WITH+CASCADED+CHECK+OPTION|169371|entityMap^0|1|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

In <code>psql</code> cli , you can use
<pre><code>\d+ &lt;yourViewName&gt;
\sv &lt;yourViewName&gt;
</code></pre>
Output as follows:
<pre><code>\d+ v_ma_students

 View &quot;public.v_ma_students&quot;
 Column | Type | Collation | Nullable | Default | Storage | De
scription
--------+-----------------------+-----------+----------+---------+----------+---
SOMETHINGS HERE

View definition:
 SELECT student.sno,
 student.sname,
 student.ssex,
 student.sage,
 student.sdept
 FROM student
 WHERE student.sdept::text = 'MA'::text;
Options: check_option=cascaded


\sv v_ma_students

CREATE OR REPLACE VIEW public.v_ma_students AS
 SELECT student.sno,
 student.sname,
 student.ssex,
 student.sage,
 student.sdept
 FROM student
 WHERE student.sdept::text = 'MA'::text
 WITH CASCADED CHECK OPTION
</code></pre>

blocks|key|387447|text|查找“CREATE+TABLE+...”的简单方法查询将使用此查询-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|387448|SHOW+TABLE+your_schema_name.your_table_name|code-block|syntax|javascript|387449|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The straightforward way to find the 'CREATE TABLE ...' query is to use this query -
<pre><code>SHOW TABLE your_schema_name.your_table_name
</code></pre>

Is there an easy way to see the code used to create a view using the PostgreSQL command-line client?

Something like the <code>SHOW CREATE VIEW</code> from MySQL.

How to see the CREATE VIEW code for a view in PostgreSQL?

PostgreSQL

MySQL

有没有一种简单的方法来查看使用PostgreSQL命令行客户端创建视图所使用的代码？类似于MySQL的SHOW CREATE VIEW。

问如何在PostgreSQL中查看视图的创建视图代码？
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL中查看视图的创建视图代码？EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL中查看视图的创建视图代码？
EN