有没有一种简单的方法来查看使用PostgreSQL命令行客户端创建视图的代码?

比如MySQL中的SHOW CREATE VIEW。


当前回答

select definition from pg_views where viewname = 'my_view'

其他回答

在psql cli中,可以使用

\d+ <yourViewName>
\sv <yourViewName>

输出如下:

\d+ v_ma_students

                               View "public.v_ma_students"
 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

在命令行客户端psql中,您可以使用以下命令:

\sv <VIEWNAME>

如果你想要一个ANSI SQL-92版本:

select view_definition from information_schema.views where table_name = 'view_name';
select definition from pg_views where viewname = 'my_view'

找到'CREATE TABLE…的查询是使用这个查询-

SHOW TABLE your_schema_name.your_table_name