有没有一种简单的方法来查看使用PostgreSQL命令行客户端创建视图的代码?
比如MySQL中的SHOW CREATE VIEW。
有没有一种简单的方法来查看使用PostgreSQL命令行客户端创建视图的代码?
比如MySQL中的SHOW CREATE VIEW。
当前回答
These is a little thing to point out. Using the function pg_get_viewdef or pg_views or information_schema.views you will always get a rewritten version of your original DDL. The rewritten version may or not be the same as your original DDL script. If the Rule Manager rewrite your view definition your original DLL will be lost and you will able to read the only the rewritten version of your view definition. Not all views are rewritten but if you use sub-select or joins probably your views will be rewritten.
其他回答
来自9.6及以上版本的好消息。视图编辑现在是原生的psql。只需调用\ev命令。视图定义将显示在您配置的编辑器中。
julian@assange=# \ev your_view_names
奖金。一些与查询缓冲区交互的有用命令。
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
在命令行客户端psql中,您可以使用以下命令:
\sv <VIEWNAME>
找到'CREATE TABLE…的查询是使用这个查询-
SHOW TABLE your_schema_name.your_table_name
select definition from pg_views where viewname = 'my_view'
select pg_get_viewdef('viewname', true)
手册中列出了所有这些功能:
http://www.postgresql.org/docs/current/static/functions-info.html